How to move items from one listbox to another, on an Excel UserForm. Move all items, or only the selected items
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.
First, create an Excel UserForm, and then follow the steps below, to add
On the UserForm, you'll add two ListBoxes:
The ListBoxes will keep their default names -- ListBox1 and ListBox2.
Next, follow these steps to add buttons between the ListBoxes.
The buttons between the listboxes will get the following names:
The buttons below the listboxes will be named cmdOK.
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.
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)
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
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.
Last updated: April 12, 2021 7:41 PM
Contextures RSS Feed