Move Listbox Items in Excel UserForm

How to move items from one listbox to another, on an Excel UserForm. Move all items, or only the selected items

Introduction

This Excel VBA example moves items from one listbox to another, on an Excel UserForm -- either all the items, or just the selected items.

Thanks to Dave Peterson, who contributed this technique to move ListBox items.

For the worksheet version of this example, go to the Excel VBA Move ListBox Items page.

Create a UserForm

First, create an Excel UserForm, and then follow the steps below, to add

• two listboxes,
• four command buttons to move the items, and a
• command button to close the form.

Create the ListsBoxes

On the UserForm, you'll add two ListBoxes:

1. Press the Alt + F11 keys, to open the Visual Basic Editor.
2. In the Project Explorer window, double-click on the UserForm, to select it. Here, the UserForm is named frmListBoxTest.
3. In the ToolBox, click the ListBox control
4. On the UserForm, draw the outline of a ListBox at the left side of the UserForm.
5. Then, use the ListBox tool again, to draw a ListBox on the right side of the UserForm, or copy and paste the first ListBox.

The ListBoxes will keep their default names -- ListBox1 and ListBox2.

Create the Buttons

Next, follow these steps to add buttons between the ListBoxes.

1. In the Toolbox, click the Command Button control.
2. On the UserForm, drag to draw an outline for the first command button, between the two listboxes.
3. Use the command button control to draw 3 more buttons, or copy and paste the first button.
4. Below the listboxes, add one more button, that will be used to close the form.

Name the Buttons

The buttons between the listboxes will get the following names:

• BTN_moveAllRight
• BTN_MoveSelectedRight
• BTN_moveAllLeft
• BTN_MoveSelectedLeft

The buttons below the listboxes will be named cmdOK.

To name a button:

Click on the top button, and in the Properties window, change the button name, and change the Caption. (Use the < and > keys to create arrows)

Then, click on each of the buttons below, and change its name and caption.

Add the UserForm Code

Finally, add the following code to the UserForm's code module. To add the code:

Right-click on the UserForm, and click View Code

Paste sample code (from the section below) at the top of the UserForm module (delete the Option Explicit line, if you copied it from the sample code)

ListBox Move Items Code

Copy the following code to the worksheet module.

Option Explicit
'Move Listbox Items in UserForm
'code from Dave Peterson
'posted on www.contextures.com

Private Sub BTN_moveAllLeft_Click()
    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox2.ListCount - 1
        Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
    Next iCtr

    Me.ListBox2.Clear
End Sub

Private Sub BTN_moveAllRight_Click()
    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox1.ListCount - 1
        Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
    Next iCtr

    Me.ListBox1.Clear
End Sub

Private Sub BTN_MoveSelectedLeft_Click()
    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(iCtr) = True Then
            Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
        End If
    Next iCtr

    For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1
        If Me.ListBox2.Selected(iCtr) = True Then
            Me.ListBox2.RemoveItem iCtr
        End If
    Next iCtr

End Sub

Private Sub BTN_MoveSelectedRight_Click()
    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
        End If
    Next iCtr

    For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox1.RemoveItem iCtr
        End If
    Next iCtr

End Sub

Private Sub cmdOK_Click()
 Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim iCtr As Long

    With Me.ListBox1
        For iCtr = 1 To 10
            .AddItem "This is a test" & iCtr
        Next iCtr
    End With

    With Me.ListBox2
        For iCtr = 1 To 10
            .AddItem "This is a not a test" & iCtr
        Next iCtr
    End With

    Me.ListBox1.MultiSelect = fmMultiSelectMulti
    Me.ListBox2.MultiSelect = fmMultiSelectMulti

End Sub

ListBox UserForm Move Items Workbook

To see the code, and test the ListBox move items code for a UserForm, you can download the ListBox UserForm Move Items sample workbook. The file is in xlsm format, and is zipped. It contains macros, so enable them if you want to test the code.

Related Links

FAQs, Excel VBA, Excel Macros

Create an Excel UserForm

UserForm with ComboBoxes

Edit a Recorded Macro

Excel Macros Getting Started