Home > Macros > UserForms > ListBox How to Move Excel ListBox ItemsUse this macro to move items from one Woksheet listbox to another; move all the items, of just the selected items. |
Thanks to Dave Peterson, who contributed this technique to move ListBox items.
This Excel VBA example moves items from one listbox to another -- either all the items, of just the selected items.
To see the step, watch this short video. Written instructions are below the video.
The listbox at the left is filled with items, when the sheet is activated. The first step is to create that list of items, and name the range.
Here, the list is on a sheet named Admin_Lists, and the list is named ItemList.
Next, you'll add the ListBoxes.
Select the sheet where the ListBoxes will be used. In this example, the sheet is named CreateRpt
On the Ribbon's Developer tab, click Insert, and click the ListBox control, under ActiveX Controls.
On the worksheet, drag to draw an outline for the ListBox, then release the mouse button.
Use the ListBox control to draw a second ListBox, or copy and paste the first ListBox.
The ListBoxes will keep their default names -- ListBox1 and ListBox2.
Next, follow these steps to add buttons between the ListBoxes.
On the Ribbon's Developer tab, click Insert, and click the Command Button control, under ActiveX Controls.
On the worksheet, drag to draw an outline for the first command button, then release the mouse button.
Use the command button control to draw 3 more buttons, or copy and paste the first button.
The buttons will get the following names:
Right-click on the button, and click Properties
In the Properties window, change the button name, and change the Caption. (Use the < and > keys to create arrows)
Finally, add the following code to the worksheet's code module. To add the code:
Right-click on the sheet tab, and click View Code
Paste sample code at the top of the worksheet module (delete the Option Explicit line, if you copied it from the sample code)
Copy this code to the worksheet module.
Option Explicit 'Move ListBox Items 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 Worksheet_Activate() Dim myCell As Range Dim rngItems As Range Set rngItems = Sheets("Admin_Lists").Range("ItemList") Me.ListBox1.Clear Me.ListBox2.Clear With Me.ListBox1 .LinkedCell = "" .ListFillRange = "" For Each myCell In rngItems.Cells If Trim(myCell) <> "" Then .AddItem myCell.Value End If Next myCell End With Me.ListBox1.MultiSelect = fmMultiSelectMulti Me.ListBox2.MultiSelect = fmMultiSelectMulti End Sub
To see the code, and test the ListBox move items macro code, you can download the ListBox Move Items sample workbook. The file is in xlsm format, and is zipped. The file contains macros, so enable them if you want to test the code.
Create an Excel UserForm Video
Last updated: March 16, 2023 4:25 PM