When you attach toolbars (don’t do that, but it’s a good example) the dialog box shows two listboxes between which you can transfer list items. This post shows you how you can do that on your own Userforms.
I’ve set up a Userform with two listboxes and four commandbuttons; lbLeft, lbRight, cmdRight, cmdRightAll, cmdLeft, and cmdLeftAll. I think the picture below should be self explanatory.
The code for the entire module is shown below. The code uses the AddItem and RemoveItem methods to transfer the data. I’m sure you’ll notice that when you transfer items back and forth, they don’t stay in the same order. You can add code that will sort the ListBoxes, but I’ll save that for another post.
Option Explicit
Private Sub cmdLeft_Click()
With Me.lbRight
'Make sure something is selected
If .ListIndex > -1 Then 'Add the selection to the other listbox
Me.lbLeft.AddItem .Value
'Remove the item from the current listbox
.RemoveItem .ListIndex
End If
End With
End Sub
Private Sub cmdLeftAll_Click()
Dim i As Long
With Me.lbRight
'Loop through the items
For i = 0 To .ListCount - 1
'Add each item to the other listbox
Me.lbLeft.AddItem .List(i)
Next i
'Clear the items from the current listbox
.Clear
End With
End Sub
Private Sub cmdRight_Click()
With Me.lbLeft
If .ListIndex > -1 Then
Me.lbRight.AddItem .Value
.RemoveItem .ListIndex
End If
End With
End Sub
Private Sub cmdRightAll_Click()
Dim i As Long
With Me.lbLeft
For i = 0 To .ListCount - 1
Me.lbRight.AddItem .List(i)
Next i
.Clear
End With
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
'Fill the left listbox with some stuff
For i = 10 To 16
Me.lbLeft.AddItem _
Format(DateSerial(2004, 5, i), "dddd")
Next i
End Sub
Does this code works for multiselect enabled listboxes
if not, where can i refer for it
Why are you using the prefix “lbl” when these are list boxes?
Also, as said above, this code does not work for multiselect listboxes.
The prefix is ‘lb’, which I guess I used for listboxes 11 years ago. Now I use lbx. I’ve never rewritten the code to work with multiselect mostly due to the apparent lack of interest on this post. But I’ll put it on the list.
Instead of selecting items first, after which you displace them to the other Listbox, you can do it in 1 go, using the doubleclick.
Private Sub UserForm_Initialize()ListBox1.List = Application.GetCustomListContents(2)
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
M_displace 1
End Sub
Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
M_displace 2
End Sub
Sub M_displace(y)
With Me("ListBox" & y)
If .ListIndex > -1 Then
Me("ListBox" & y + IIf(y = 1, 1, -1)).AddItem .List(.ListIndex)
.RemoveItem .ListIndex
End If
End With
End Sub