Home > Macros > UserForms > ListBox How to Move Excel ListBox Items Use this macro to move items from one Woksheet listbox to another; move all the items, of just the selected items.

Introduction

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.

Create an Item List

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.

Create the Lists Boxes

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.

Create the Buttons

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.

Name the Buttons

The buttons will get the following names:

• BTN_moveAllRight
• BTN_MoveSelectedRight
• BTN_moveAllLeft
• BTN_MoveSelectedLeft

To name a button:

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)

Add the Worksheet Code

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)

ListBox Move Items 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

Get the Workbook

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.

Related Excel VBA Tutorials

FAQs, Excel VBA, Excel Macros  

Create an Excel UserForm   Video

UserForm with ComboBoxes

Excel VBA Edit Your Recorded Macro

Excel VBA Getting Started