Move Excel Listbox Items

To select specific items from a long list, you can create 2 ListBoxes on a worksheet.

Then, use arrow buttons to move all the items from one list to the other, or move selected items only.

ListBoxes on Worksheet

In the screen shot below, there are 2 ListBoxes, and 4 buttons with arrows, on a worksheet named CreateRpt.

• In the ListBox at the left, you can select one or more items that you’d like in a report.
• Then, click the 2nd button, with one arrow that point to the right.
• The selected items will move to the ListBox on the right.

OR

• You could click the. top button, to move all the items to the right-hand ListBox.

Items List on Worksheet

When you get started, the list at the left is filled with items. Those items come from a named range, on a worksheet named Admin.

You could modify that list, to include the items that you need in your ListBox.

Use the ListBoxes

In the sample file that you can download, there is a Menu worksheet, with a navigation button, “Select Report Items”.

Click that button to go to the CreateRpt worksheet, where the ListBoxes are located.

Macro Runs Automatically

When the CreateRpt sheet is activated, an event procedure (macro) runs automatically.

The VBA code in that event procedure does the following steps:

• Clears both ListBoxes
• Adds all items from worksheet list, in ListBox at the left

Back to Menu Sheet

On the CreateRpt sheet, there is another navigation button, “Menu”, at the top left of the sheet.

Click that button, and a macro runs, to take you back to the Menu sheet.

Navigation Button Macros

The two navigation buttons run simple macros, and the VBA code is shown below.

First, here is the code that the Menu button runs:

Sub GoMenu()
Worksheets("Menu").Activate
End Sub

Next, here is the code that the Select Report Items button runs:

Sub GoRpt()
Worksheets("CreateRpt").Activate
End Sub

Read the Detailed Instructions

Thanks to Dave Peterson, who sent me the instructions and sample code for moving listbox items.

To see the details on how this technique works, visit the Excel VBA Move Listbox Items page on the Contextures website.

The tutorial shows, step by step,  how to set up the worksheets, add the ListBoxes, and create the named ranges.

It also shows all of Dave’s sample code, for moving the items between the ListBoxes.

Download the Move Listbox Items Sample File

To see the code, and test the Listbox move items code, you can download the Listbox Move Items sample workbook.

The file is in Excel 2007 format, and is zipped. It contains macros, so enable them if you want to test the code.

Video: Move Listbox Items on Worksheet

To see the steps to create the Listbox example, you can watch this Excel video tutorial.

______________