ListBox VBA

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.

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.


    ListBox VBA

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


    ListBox VBA

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


    ListBox VBA

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


    ListBox VBA

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


    ListBox VBA

  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.


    ListBox VBA

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


    ListBox VBA


    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:

You can download this List Boxes Excel Template here List Boxes Excel Template
  • 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.
ListBox VBA
  • Step 3: In ListFillRange, enter Month.
ListBox VBA
  • Step 4: The list box appears as shown in the succeeding image.
ListBox VBA

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

ListBox VBA

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.
ListBox VBA
  • Step 2: In the Insert menu, click UserForm.
ListBox VBA
  • Step 3: From the toolbox, drag the list box control on the UserForm.
ListBox VBA
  • Step 4: Double-click on UserForm and select UserForm event, as shown in the following screenshot.
ListBox VBA
  • 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

ListBox VBA
  • 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 VBAUserForm Using VBAIn VBA, userforms are customized user-defined forms that are designed to accept user input in the form of a form. It has various sets of controls to add such as text boxes, checkboxes, andlabels.read more are listed as follows:

  • Step 1: From the toolbox, add a CommandButton on the UserForm.
ListBox VBA
  • Step 2: Right-click on the Commandbutton and click properties.
ListBox VBA
  • Step 3: Change the caption of the CommandButton to Create_Listbox.
ListBox VBA
  • Step 4: Double-click on the command button and the following code appears.

Private Sub CommandButton1_Click()

End Sub

ListBox VBA
  • 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 formulaVLOOKUP FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more as shown in the following images.

ListBox VBA
ListBox VBA

The Constants of List Box in VBA

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

ListBox VBA

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 namesJames, 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([begin cell]:[end cell],[cell link],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.

Key Takeaways
  • 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

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

  • Edit Drop-Down List in Excel
  • Checkbox in Excel
  • VBA Macros Training
  • VBA MsgBox
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>