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:
- Step 1: Enable the Developer tab on ExcelDeveloper Tab On ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu. 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()
- 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, and labels. 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()
- Step 5: Name the following procedure as “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
- 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”
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:
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 are unique identifiers. 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
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.
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 this code, the list box is populated with 5 names–James, Kelly, Elizabeth, George, and William.
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.
The differences between a list box and a combo boxCombo BoxCombo Box in Excel is a type of data validation tool that can create a dropdown list for the user to select from the pre-determined list. It is a form control which is available in the insert tab of the developer’s tab. 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
– 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
- 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 –