change language:

Setting up drag and drop for list boxes on a VBA form is not very easy and requires very careful work. The following three events play a role in drag and drop: MouseMove, BeforeDragOver and BeforeDropOrPaste. The drag and drop is initiated in the MouseMove event. The list items to be moved or copied are copied into a DataObject. When configured correctly, the mouse drag is handled by the BeforeDragOver event. After all, the actual drop is handled by the BeforeDropOrPaste event. During the drag and drop, all other events on the form are disabled.

set up listbox drag and drop

To make it very easy to set up drag and drop for list boxes on a form, this article uses class modules. All drag and drop logic is contained in these classes. This way, by adding just a few lines of code to a form, drag and drop can be set for all listboxes very easily. Nothing else needs to be changed on the form itself.

First, a limited basic structure of this structure will be discussed. This is followed by an advanced versatile version, in which the drag and drop can be very precisely configured per form. An example file of both elaborations can be downloaded.

Simple basic setup

The basic setup for enabling drag and drop consists of 2 class modules. The first class clDragDropForm links to the form. The other class clDragDropListBox is used to handle the listbox events. The code of class clDragDropForm is as follows:

'--------------------------------------------------------------------------------------------------------------------------
' Author    : Manfred van den Noort
' Copyright : © 2020 worksheetsvba.com, all rights reserved
' Date      : 2020-12-07
' Version   : 1.0
' Purpose   : Set up ListBox Drag & Drop Basic
'--------------------------------------------------------------------------------------------------------------------------

Private frmDragDrop As MSForms.UserForm
Private lstbxDragSource As MSForms.ListBox
Private colListBoxes As Collection

Private Sub Class_Initialize()
    Set colListBoxes = New Collection
End Sub

Friend Property Set DragDropForm(ByRef oFrm As Object)
    Set frmDragDrop = oFrm
End Property

Friend Property Set DragSource(ByRef oListBox As MSForms.ListBox)
    Set lstbxDragSource = oListBox
End Property

Friend Property Get DragSource()
    Set DragSource = lstbxDragSource
End Property

Friend Sub GetListboxes()
    Dim ctl As Control, oDragDropListBox As clDragDropListbox
    For Each ctl In frmDragDrop.Controls
        If TypeName(ctl) = "ListBox" Then
            Set oDragDropListBox = New clDragDropListbox
            With oDragDropListBox
                Set .ParentForm = Me
                Set .DragDropListBox = ctl
            End With
            colListBoxes.Add oDragDropListBox
        End If
    Next
End Sub

Friend Sub TerminateDragDrop()
    Set colListBoxes = Nothing
End Sub

To set day and drop for the list boxes on a form, an instance of this class is created with a few lines of code. The necessary other objects of class clDragDropListBox are then automatically created from this class. This is the code of class clDragDropListBox:

'--------------------------------------------------------------------------------------------------------------------------
' Author    : Manfred van den Noort
' Copyright : © 2020 worksheetsvba.com, all rights reserved
' Date      : 2020-12-07
' Version   : 1.0
' Purpose   : Set up ListBox Drag & Drop Basic
'--------------------------------------------------------------------------------------------------------------------------

Private oParentForm As clDragDropForm
Private WithEvents LstBx As MSForms.ListBox

Friend Property Set ParentForm(ByRef oValue As clDragDropForm)
    Set oParentForm = oValue
End Property

Friend Property Set DragDropListBox(ByRef oValue As MSForms.ListBox)
    Set LstBx = oValue
End Property

Private Sub LstBx_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As Long, ByVal Data As MSForms.DataObject, ByVal x As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    Dim i As Long
    If oParentForm.DragSource Is LstBx Then Exit Sub
    Cancel = True
    Effect = fmDropEffectMove
    With LstBx
        .AddItem Data.GetText
        .ListIndex = .ListCount - 1
    End With
    With oParentForm.DragSource
        For i = .ListCount - 1 To 0 Step -1
            If .Selected(i) Then
                If .List(i) = Data.GetText Then
                    .RemoveItem i
                    Exit For
                End If
            End If
        Next i
        .ListIndex = -1
    End With
    LstBx.SetFocus
End Sub

Private Sub LstBx_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal x As Single, ByVal Y As Single, ByVal DragState As Long, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    Cancel = True
    If oParentForm.DragSource Is LstBx Then
        Effect = fmDropEffectNone
    Else
        Effect = fmDropEffectMove
    End If
End Sub

Private Sub LstBx_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    Dim oDataObject As DataObject, lEffect As Long
    If Button = 1 Then
        If LstBx.Text = "" Then
            Exit Sub
        End If
        Set oDataObject = New DataObject
        oDataObject.SetText LstBx.Value
        Set oParentForm.DragSource = LstBx
        lEffect = oDataObject.StartDrag
    End If
End Sub

With this code, drag and drop can be set for any listbox on a form, moving a list item and adding it to the bottom of the drop list.

To make this work on a form, a variable must be added to the top of the form's code screen:

Private oDragDropForm As clDragDropForm

After that, the following 5 lines of code must be added to the UserForm_Initialize event:

Private Sub UserForm_Initialize()
    Set oDragDropForm = New clDragDropForm
    With oDragDropForm
        Set .DragDropForm = Me
        .GetListboxes
    End With
End Sub

Finally, the following line of code must be added to the UserForm_QueryClose event:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    oDragDropForm.TerminateDragDrop
End Sub

This last line is important and should absolutely not be left out. This is because all created objects are cleaned up again with this code line. The so-called 'garbage collector' of VBA cannot automatically clean up these objects when the form is closed, because these objects are interconnected. If this code line is omitted, the objects will remain in memory and a so-called 'memory leak' will occur..

Download an example file of the basic version:
zipSet up listbox drag and drop basic 1.0

Advanced elaboration

The drag and drop from the previous section works by itself, but is quite basic and cannot be configured further.An advanced version has therefore been developed in which the drag and drop per form can be accurately configured. This version also works with multi-column list boxes and multi-select list boxes. The following parameters can be set:

  1. Drop effect: determine whether the list item should be copied (CopyItem) or moved (MoveItem (default)).
  2. Drop type: determine how to add the item. This can be in alphabetical order (Alphabetic), at the end of the list (BottomOfList (default)), insert at the location of the mouse pointer (CursorPosition) or in numerical order (Numeric).
  3. Allow drop in own list: Yes or No (default). This setting only takes effect if CursorPostion is selected for DropType. This setting does not make sense with other DropTypes.

In addition, it can also be specified with optional parameters that the drag and drop may only take place between list boxes with the same group name. Suppose a form contains 4 list boxes and there may only be mutual drag and drop between listbox1 and 2 and there may only be mutual drag and drop between list boxes 3 and 4. This can then be set as follows:

.GetListboxes ListBox3.Name & "~grp2", ListBox4.Name & "~grp2"

So the group name must be added to the listbox name separated by a tilde (~). Because the default group name is an empty string, only 1 group name has to be entered in this case.A different group name may also be specified for listbox1 and listbox2, but that is not necessary in this case. Instead of ListBox3.Name & "~ grp2" can also be given "ListBox3 ~ grp2" as parameter. The advantage of using ListBox3.Name is that an error message occurs when the name of the ListBox3 is changed. This error message alerts you that ListBox3.Name also needs to be changed. This reduces the risk of errors in the configuration.

A difficulty with drag and drop is that it is not easy to determine in VBA which list item the mouse pointer is over. The Y value of the mouse position is included with the ListBox events, but this cannot be easily converted to a list item. That is why a special algorithm has been created to determine the height of a list item. The row height can only be determined if all visible rows of the Listbox are filled with list items. If this is not the case, extra rows are temporarily added until all visible rows are filled. With this it can ultimately be determined over which list item the mouse pointer is located.

Another difficulty concerns working with multiselect list boxes. For example, if you select 3 values ​​and then click on a list item to move these 3 items to another listbox, clicking this last value will be DEselected again. This is very confusing for the user. That is why the item that is clicked is also included in multiselect list boxes. With multiselect listboxes, dropping in the own list is disabled. This is because this works very unreliable. The chance is then too great that a drag and drop action is unintentionally performed while the user is busy selecting items. Maybe this option will be added in a next version.

An interface class has also been added to simplify the configuration. This ensures that, when configuring the drag and drop on a form, only the properties and methods that are important for the configuration are shown. The other properties and methods are then not available as options.

The code for this advanced version is quite extensive and complex and is therefore not included here on this page.The full code can be found in the sample file that can be downloaded:

Download an example file of the advanced version:
zipSet up listbox drag and drop advanced 1.0
 

Questions / suggestions

Hopefully, this article helped you set up list box drag and drop on a VBA userform. If you have any questions about this topic or suggestions for improvement, please post a comment below.

arrow_up