Moving Data Between ListBoxes

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.

lbtransfer1

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

5 thoughts on “Moving Data Between ListBoxes

  1. Why are you using the prefix “lbl” when these are list boxes?

    Also, as said above, this code does not work for multiselect listboxes.

  2. 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.

  3. 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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.