The Excel VBA ListBox is a list control that allows you to select (or deselect) one or more items at time. This is compared to the VBA ComboBox which only allows you to select a single items from a drop down list. Let us explore how to create, clear and make a VBA ListBox let you select multiple items.
Create a Excel Form ListBox (Form Control)
To create an Excel Form ListBox you need to first make sure you can see the Developer Tab. Next go to Controls and select the ListBox. Place the ListBox on the Worksheet. You can right click on it to set the properties.
Using the Form ListBox
To add items to set items in a From ListBox is easiest by providing an Excel Range on your Worksheet that contain values for options.
Select Excel Range as ListBox Items
- Set the Input range for the items in the Form ListBox
- Set the Cell Link range for the selected item
The Excel Form ListBox is an option to use if you need a simple way to limit input options for your users in an spreadsheet.
Beware in below examples I am using the ActiveX ListBox instead!
Create a VBA ListBox (ActiveX)
Now let us explore the adding Items to a Listbox using VBA. For this purpose it is easier and more convenient to use an ActiveX ListBox control (which I call the VBA ListBox) instead of the previously used Form ListBox:
In examples below I will be adding code to the Worksheet module in which I added a ActiveX ListBox control named ListBox1.
Add Items to ListBox
To add VBA Add Items to the VBA ListBox use the AddItem function.
'Syntax AddItem ( itemValue, itemOrder ) 'itemValue - the value you want to add to your list 'itemOrder - the position in the VBA ListBox at which you want to insert your item (first is 0)
Examples of adding items to a ActiveX ListBox with VBA:
'Add Item Car ListBox1.AddItem "Car" 'Add Item Bus ListBox1.AddItem "Bus" 'Add Item Plane as second ListBox1.AddItem "Plane", 1 'Add Item Plane ListBox1.AddItem "Tesla"
Clear items in ListBox
To Clear/Empty items in a VBA ListBox simply use the Clear function:
'Add Item Car ListBox1.AddItem "Car" 'Remove all items in the ListBox ListBox1.Clear
Remove item from ListBox
To remove an item at a specific index or position in a VBA ListBox you need to use the RemoveItem function. Remember that the index of the first element is 0:
ListBox1.AddItem "Car" ListBox1.AddItem "Plane" ListBox1.AddItem "Bus" 'Remove the item "Plane" ListBox1.RemoveItem(1)
Count items in ListBox
Counting items in a ListBox can be done using the Count function:
ListBox1.AddItem "Car" ListBox1.AddItem "Plane" ListBox1.AddItem "Bus" 'Count items Debug.Print ListBox1.ListCount 'Result: 3
Multiple Selection VBA ListBox
To enable multiselection on your VBA ListBox (ActiveX) use the MultiSelect property. The property can have one of the following values:
- fmMultiSelectSingle – default property. You can only select 1 item
- fmMultiSelectMulti – you can select multiple items. Clicking on an item will select (include it in the existing selected items) or deselect it (remove it from the existing selected items)
- fmMultiSelectExtended – you can select multiple items. However, when you click on any item it will only select the current item. To select multiple items you need to click and hold and move the mouse up/down to select more items
By setting the MultiSelect option:
ListBox1.MultiSelect = fmMultiSelectExtended
Select / Deselect items in ListBox
First we will try to understand how to check if an item on our ListBox is selected or not. For this we will use the Selected property.
ListBox1.AddItem "Car" ListBox1.AddItem "Plane" ListBox1.AddItem "Bus" '...Click on Plane... 'Check which is selected Debug.Print ListBox1.Selection(0) 'Result: False - Car is not selected Debug.Print ListBox1.Selection(1) 'Result: True - Car is not selected Debug.Print ListBox1.Selection(2) 'Result: False - Bus is not selected
To Select or Delect an item simply set the property to True (Selected) or False (Deselected):
ListBox1.AddItem "Car" ListBox1.AddItem "Plane" ListBox1.AddItem "Bus" 'Select Car ListBox1.Selection(0) = True 'Check which is selected Debug.Print ListBox1.Selection(0) 'Result: True - Car is not selected Debug.Print ListBox1.Selection(1) 'Result: False - Car is not selected Debug.Print ListBox1.Selection(2) 'Result: False - Bus is not selected