Home > Macros > UserForms > Listbox Move Listbox Items in Excel UserFormHow to move items from one listbox to another, on an Excel UserForm. Move all items, or only the selected items |
IntroductionThis 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 UserFormFirst, create an Excel UserForm, and then follow the steps in the sections below, to add
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 |
Add Two ListBoxesNext, follow these steps to create the first ListBox.
|
Create Second ListBoxNext, follow these steps to create the second ListBox:
Note: For this project, the two ListBoxes will keep their default names -- ListBox1 and ListBox2. |
Add UserForm CodeFinally, 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.
|
ListBox Move Items CodeCopy the following code to the UserForm code module (follow the steps in the Add UserForm Code section, above.. The code has the following procedures:
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 WorkbookTo 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 |
Last updated: December 6, 2022 9:46 PM