Return to VBA Code Examples

VBA Listbox

This tutorial will demonstrate how to work with Listboxes using VBA.

In VBA, you can create a Listbox where a user can choose one of the listed options. Listboxes are often used in UserForms, but can also be used in a Worksheet. In this tutorial, you will learn how to create, populate and delete a Listbox. You will also see how to get a user choice in VBA and use it in code.

If you want to learn how to create a ComboBox, click here: VBA ComboBox

If you want to learn how to create a Checkbox, click here: VBA Checkbox

 

Create a Listbox

In order to insert a Listbox in the Worksheet, you need to go to the Developer tab, click Insert and under ActiveX Controls choose List Box:

Vba Insert Listbox

Image 1. Insert a Listbox in the Worksheet

 

When you select the Listbox which you inserted, you can click on Properties under the Developer tab:

Vba Listbox Properties

Image 2. Change Listbox Properties

Here you can set different properties of the Listbox. For the beginning, we changed the attribute Name to lstListBox. Now, we can use the Listbox with this name in VBA code.

Populate a Listbox in VBA code

First, we need to populate the Listbox with values. In most cases, a Listbox needs to be populated when the Workbook is opened. Because of this, we need to put a code for populating the Listbox in object Workbook, procedure Open. This procedure is executed every time a user opens the Workbook. Here is the code:

With Sheet1.lstListBox
    .AddItem "John"
    .AddItem "Michael"
    .AddItem "Jennifer"
    .AddItem "Lilly"
    .AddItem "Robert"
End With

As you can see in Image 3, we populated our Listbox with 5 names (John, Michael, Jennifer, Lilly and Robert):

Populate Listbox in Vba

Image 3. Populate the Listbox in VBA

 

Populate a Listbox From a Cells Range

Another possible way to populate a Listbox is to let a user do it. A Listbox can be linked to a range of cells. Therefore, every time a user enters a new value in the cells range, the Listbox will update with that value.

If you want to enable this, you have to go to the Properties of the ListBox and set the attribute ListFillRange:

Vba Populate Listbox From Cells Range

Image 4. Populate the Listbox from the cells range

 

We linked our Listbox with the range E2:E5, where we put names we want (Nathan, Harry, George, Roberta). As a result, the Listbox is now populated with these names.

 

Get a Selected Item of a Listbox in VBA

The purpose of a Listbox is to get a users choice. In order to retrieve a value that is selected by a user, you need to use this code:

Dim strSelectedItem As Variant

strSelectedItem = Sheet1.lstListBox.Value

The users selection is in the attribute Value of Sheet1.lstListbox object. This value is assigned to the variable strSelectedItem:

Vba Listbox Get Selected Value

Image 5. Get a selected value from the Listbox in VBA

 

We selected George in the Listbox and executed the procedure. As you can see in Image 5, the value of the strSelectedItem is George, which is the value we selected. Furthermore, you can process this variable in the code.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

Clear a Listbox

In order to clear a Listbox in VBA, you need to use Clear method of Sheet1.lstListBox object. It will delete all the items from the Listbox. Here is the code:

Sheet1.lstListBox.Clear

(Note: If the attribute ListFillRange is set, the above code will produce an error.
You have to clear the attribute ListFillRange first.)

When we execute the code, we get the empty Listbox:

Vba Clear Listbox

Image 6. Clear the Listbox

 

Use a Listbox in a UserForm

As we mentioned, Listbox is most often used in UserForms. To explain how you can do it, we will first insert an Userform. In VBA Editor, right-click on Module name, click on Insert and choose UserForm:

Vba Listbox Insert in Userform

Image 7. Insert a Userform

 

To display controls for inserting, you need to enable the Toolbox. To do this, click on the Toolbox icon in the toolbar. After that, you will get the windows with all the controls available. You can click on ListBox to create it in the Userform.

Vba Insert Listbox in Userform

Image 8. Insert a Listbox in the Userform

 

 

We will name the ListBox lstListBox. In order to populate it with values, we need to put the following code into the method Initialize of the object UserForm:

Private Sub UserForm_Initialize()
    
    With UserForm1.lstListBox

        .AddItem "John"
        .AddItem "Michael"
        .AddItem "Jennifer"
        .AddItem "Lilly"
        .AddItem "Robert"

    End With

End Sub

This code triggers every time a user runs the Userform and populates the Listbox with these 5 names:

Vba Listbox in Userform

Image 9. The Listbox with values in the Userform

 

If you want to get selected value from the ListBox, you need to use the same logic for the Listbox in a Worksheet, which is explained earlier in the article.