Home > Macros > UserForms > Listbox

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.

NOTE: There is also a worksheet version of this technique, if you don't want to build a UserForm. For details and the sample file, go to the Excel VBA Move ListBox Items page.

Create a UserForm

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

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

This video shows how to create the UserForm, and give it a name and a caption.

There are more UserForm videos on the Basic UserForm for Data Entry page, and written steps, and sample files to download, on the Create an Excel Userform page

Create the ListsBoxes

On the UserForm, you'll add two ListBoxes, side-by-side.

Follow these steps to open the UserForm that you created

1. First, press the Alt + F11 keys, to open the Visual Basic Editor (VBE).
2. At the left, in the Project Explorer window, find the workbook that contains your UserForm
3. If the Forms folder is closed, click the plus sign beside it, to open the folder
4. In the Forms folder, double-click on the UserForm, to select it.
   • Here, the UserForm is named frmListBoxTest.

Add Two ListBoxes

Next, follow these steps to create the first ListBox.

1. If the Toolbox is not open, click the Toolbox command on the Menu Bar
• OR, click the View menu, and then click the Toolbar command
2. In the ToolBox, click the ListBox control, to activate that tool
3. Next, on the UserForm, draw the outline of a ListBox at the left side of the UserForm.
• I made a ListBox about 2" wide and 4" tall
• You'll need about an 1" of empty space between the ListBoxes, for buttons, so don't make the ListBoxes too wide

Create Second ListBox

Next, follow these steps to create the second ListBox:

• On the UserForm, click on the first ListBox, to select it
• To copy the selected ListBox, use the keyboard shortcut - Ctrl + C
• Next, to paste the copy onto the ListBox, use the keyboard shortcut - Ctrl + V
• Point to the second ListBox, and drag it to the right

Note: For this project, the two 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 button 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 UserForm Code

Finally, you'll add Excel VBA code to the UserForm's code module.

This code runs when you open the UserForm, or click one of the buttons on the UserForm.

• Follow these steps to add the VBA code to the UserForm:
• First, go down to the Code section on this page, and copy all of the code that's in the grey box -- start with the Option Explicit line, and copy everything down to (and including) the End Sub line
• Next, go back to the UserForm in your Excel workbook
• Right-click on an empty part of the UserForm, and in the pop-up menu, click View Code

• Select any code that's already on the UserForm's code module, and delete it
• Right-click at the top of the UserForm's code mode, and click the Paste command
• The copied code is added to the code module, and thin lines appear between the procedures (above the Sub lines)

ListBox Move Items Code

Copy the following code to the UserForm code module (follow the steps in the Add UserForm Code section, above..

The code has the following procedures:

• There are four procedures for the Move buttons, to move the list items left or right, between the two listboxes.
• There is also a procedure for the OK button, to close the UserForm
• The final procedure, UserForm_Initialize, runs when you open the UserForm. It creates a list of numbered items in each listbox, for testing.

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.

In the Excel file, there is a worksheet with a button you can click, to open the UserForm, and test it.

Related Links

FAQs, Excel VBA, Excel Macros

Create an Excel UserForm

UserForm with ComboBoxes

Edit a Recorded Macro

Excel Macros Getting Started