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.
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.
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:
- Drop effect: determine whether the list item should be copied (CopyItem) or moved (MoveItem (default)).
- 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).
- 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:
Drag en drop for listboxen on a worksheet
It is also possible to set up drag and drop for ActiveX listboxes placed on a worksheet. The code has to be adjusted in a number of places, but the basic principle remains the same. An example file can be downloaded below in which this can be seen. This example is based on the simple drag drop basic setup from this article.
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.
After a quick look it seems that this should also be possible with ActiveX listboxes on a worksheet.
When I have some more time, I'll dig into this further and make an example with drag and drop with listboxes on a worksheet.
Great code, thanks a lot!
In my case I needed a "copy" functionality - so that when Ctrl is held - item is not moved, but being duplicated.
I found that it's quite easy to do. So, here is a little recipe if someone once will need it too.
I've updated clDragDropListbox LstBx_BeforeDropOrPaste a little bit:
...
...
If oParentForm.DropEffect = MoveItem Then
With oParentForm.DragSource
If Shift >= 1 Then ' If CTRL is pushed, variable Shift has value 2, if CTRL + SHIFT - value is 3
...
end if
End With
LstBx.SetFocus
End If
If you want to use the Ctrl key to copy the item instead of moving it, I think your suggestion is not quite right. I think you should use If Shift is less than 2 (or If Shift = 0) instead of greater than or equal to 1.