VBA List Box

Last Updated :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Vandana Kataria

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Table of contents

How to Create List Box in Excel?

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

  1. 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.


    List-Box-in-Excel-1

  2. Step 3: Draw a rectangle in the Excel worksheet to create a list box, as shown in the following image.


    List-Box-in-Excel-2

  3. Step 4: Select the list box, go to the Developer tab, and select "properties". The "properties" window opens.


    List-Box-in-Excel-3

  4. Step 5: In the "properties" window, enter "days" in "ListFillRange". "Days" is the named range for weekdays.


    List-Box-in-Excel-4

  5. Step 6: Click the "MultiSelect" property. From the drop-down list, select "1- fmMultiSelectMulti" as shown in the succeeding image.


    List-Box-in-Excel-5

  6. 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.


    List-Box-in-Excel-6

  7. Step 8: Close the "properties" window. The list box appears as shown in the following image.


    List-Box-in-Excel-7

    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.
List Box in Excel 8
  • Step 3: In “ListFillRange,” enter “Month.”
List Box in Excel 9
  • Step 4: The list box appears as shown in the succeeding image.
List Box in Excel 10

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

List Box in Excel 11

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.
List Box in Excel 12
  • Step 2: In the Insert menu, click “UserForm.”
List Box in Excel 13
  • Step 3: From the toolbox, drag the list box control on the “UserForm.”
List Box in Excel 14
  • Step 4: Double-click on “UserForm” and select “UserForm event,” as shown in the following screenshot.
List Box in Excel 15
  • 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

List Box in Excel 16
  • 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.”
List Box in Excel 17
  • Step 2: Right-click on the “Commandbutton” and click “properties.”
List Box in Excel 18
  • Step 3: Change the caption of the “CommandButton” to “Create_Listbox.”
List Box in Excel 20
  • Step 4: Double-click on the command button and the following code appears.

Private Sub CommandButton1_Click()

End Sub

List Box in Excel 21
  • 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.

List Box in Excel 22
List Box in Excel 23

The Constants of List Box in VBA

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

List Box in Excel 24

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

Recommended Articles

This has been a guide to List Box in Excel VBA. Here we discuss how to create List Box in Excel Worksheet and on UserForms, along with practical examples and downloadable templates. You may also look at these useful functions in Excel –