VBA List Box

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

What is List Box in Excel VBA?

The list box in Excel VBA is a list assigned to a variable. This list has various inputs to select from and allows selecting multiple options at once. A list box can be inserted on a UserForm by choosing the list box option.

List boxes use named ranges having certain values. The purpose of using a list box is to display a list of values that users can choose from.

What is List Box in Excel VBA?

How to Create List Box in Excel?

The steps to create a list box in Excel are listed as follows:

Step 1: Enable the Developer tab on Excel ribbon to access ActiveX controls. In the Excel Options dialog box, click "customize ribbon in Excel" and select the Developer checkbox.
Step 2: In the Developer tab, click on Insert, followed by the "list box" control. This inserts a new list box in the Excel worksheet.
Step 3: Draw a rectangle in the Excel worksheet to create a list box, as shown in the following image.
Step 4: Select the list box, go to the Developer tab, and select "properties". The "properties" window opens.
Step 5: In the "properties" window, enter "days" in "ListFillRange". "Days" is the named range for weekdays.
Step 6: Click the "MultiSelect" property. From the drop-down list, select "1- fmMultiSelectMulti" as shown in the succeeding image.
Step 7: Click the "ListStyle" property. From the drop-down list, select "1- fmListStyleOption". This adds checkboxes to the left of the listed items, as shown in the following image.
Step 8: Close the "properties" window. The list box appears as shown in the following image.

Note: You can select "design mode off" by clicking Design Mode. This closes the "design mode".

How to Create a List Box in VBA?

The steps to create a list box in VBA are listed as follows:

Step 1: Right-click on the list box and make sure the Design Mode is selected. This links the list box to a cell.
Step 2: In “properties,” go to the “LinkedCell” property and fill in “B17” as shown in the following image.

Step 3: In “ListFillRange,” enter “Month.”

Step 4: The list box appears as shown in the succeeding image.

The data in cell B17 changes when you change your choice in the list box. This is shown in the following image.

How to Create a List Box on a UserForm?

The steps to create a list box on a UserForm are listed as follows:

Step 1: In the Developer tab, click “Visual Basic” from the Code or Press Alt+F11.

Step 2: In the Insert menu, click “UserForm.”

Step 3: From the toolbox, drag the list box control on the “UserForm.”

Step 4: Double-click on “UserForm” and select “UserForm event,” as shown in the following screenshot.

Step 5: Add the following code to the in-between procedure.

Private Sub UserForm_Initialize()

ListBox1.AddItem "MBA"

ListBox1.AddItem "MCA"

ListBox1.AddItem "MSC"

ListBox1.AddItem "MECS"

ListBox1.AddItem "CA"

End Sub

Step 6: Press F5 to run the code.

How to Add a Dynamic Control to List Box?

The steps to add a dynamic control to the list box on the UserForm using VBA are listed as follows:

Step 1: From the toolbox, add a “CommandButton” on the “UserForm.”

Step 2: Right-click on the “Commandbutton” and click “properties.”

Step 3: Change the caption of the “CommandButton” to “Create_Listbox.”

Step 4: Double-click on the command button and the following code appears.

Private Sub CommandButton1_Click()

End Sub

Step 5: Name the following procedure as “Add_Dynamic_Listbox.”

Sub Add_Dynamic_Listbox()

'Add Dynamic List Box and assign it to object 'LstBx'

Set LstBx = UserForm3.Controls.Add("Forms.ListBox.1")

'List Box Position

LstBx.Left = 20

LstBx.Top = 10

End Sub

Step 6: Click F5 to run the macro.

How to add items to list box control in VBA?

The code to add items to list box control in VBA is mentioned as follows:

Private Sub Insert _Items _To_LstBox ()

ListBox1.AddItem "Item 1"

ListBox1.AddItem "Item 2"

ListBox1.AddItem "Item 3"

ListBox1.AddItem "Item 4"

ListBox1.AddItem "Item 5"

End Sub

How to clear items from list box control using VBA?

The code to clear items from the list box control in VBA is mentioned as follows:

Sub Clr_LstBx()

UserForm3.ListBox1.Clear

End Sub

Example

Let us say we have daily sales data. We want to prepare a graphical user interface of this sales data that allows us to select items from a list box. To fetch the sales details, we have to change the Index cell to “cell link” and apply the VLOOKUP formula as shown in the following images.

The Constants of List Box in VBA

The constants, their values, and description are given in the following table:

Frequently Asked Questions

#1 - How to use a list box in Excel?

The list boxes are used to manage tables within a UserForm. The rows of information are managed neatly without an unnecessary display of information within the UserForm.

A user can scroll through the items of the list box. The setting of the "selection type" allows users to select multiple options from a list box. As the user selects an item from the list box, an action is undertaken with such selection.

By default, the list box gives an output that matches the index number of the selected item. Thus, if the fourth item is selected, the output will be number 4.

#2 - How to populate a list box in Excel VBA?

Usually, the list box needs to be populated with values every time the Workbook is opened. The procedure "Open" is executed every time the Workbook opens.

The following code must be used to populate a list box:

With Sheet1.lstListBox
.AddItem "James"
.AddItem "Kelly"
.AddItem "Elizabeth"
.AddItem "George"
.AddItem "William"
End With

With this code, the list box is populated with 5 names–James, Kelly, Elizabeth, George, and William.

#3 - How to retrieve data from a list box in Excel?

The data can be used effectively only if it is retrieved properly. The steps to retrieve data from a list box are mentioned as follows:

1. Select "Format Control" from the menu after right-clicking the list box.
2. In the box titled "Cell Link", type the coordinates of an empty cell.
3. Select the cell in which data from the list box will be displayed. The following formula must be used:
"=INDEX(:,,0)"

Note: The "begin cell" stands for the first cell of the list. The “end cell” stands for the last cell of the list. The "cell link" stands for the cell whose coordinates were typed in the second step.

#4 - What is the difference between a list box and a combo box?

The differences between a list box and a combo box are listed as follows:

- A list box consists of a list of items and allows users to select one or more options. In contrast, a combo box consists of a combination of a text box and a drop-down list. The text box enables users to type an item. The drop-down list allows users to select an item.
- A list box displays all items in a given area at once, while a combo box displays one item at a time. The items of the drop-down list of a combo box are visible when the user clicks
it.
- In a list box, the user has to choose from the available items, whereas in a combo box, a user can type an item that is not there in the list.
- A list box is used where the input is restricted to the items on the list. However, a combo box is used where a list of suggested choices is mentioned.
- A list box uses more space because the full list is displayed at once. On the other hand, a combo box fits in a small space because the list is displayed only on the clicking of the
down arrow.

A list box in Excel is a list assigned to a variable that can be used to select multiple items.
On a UserForm, a list box can be inserted by selecting the list box option.
While creating a list box in Excel, the details in “ListFillRange,” “MultiSelect,” and “ListStyle” properties have to be entered.
The “design mode” can be switched off by clicking the “Design Mode.”
While creating a list box on a UserForm, either click “Visual Basic” or press Alt+F11