List box in VBA is a list which is assigned to a variable and the list has various inputs to select upon, from the list box we can select multiple options at once, list box can be made to a userform in excel VBA by selecting the list box option and drawing it on the userform for a list box we have a named range which has certain values provided to select in a list box.
What is List Box in Excel VBA?
A list box is a list from where a user can select an item. List Box in Excel VBA can be used if you want to display a list of values that users can choose from.
How to Create List Box in Excel?
To create the List Box in excel worksheet, follow these steps:
First, you need to enable the Developer tab excel visible on the ribbon so you can get to the VBA and the ActiveX control commands. To get the Developer tab, bring up the Excel Options dialog box, click Customize Ribbon in excel, and select the Developer check box.
- Click on the Excel Ribbon then select the Developer tab and then click on Insert then click the List Box control to insert the new list box in excels worksheet.
- After that draw a rectangle in the excel worksheet to create the List Box in excel.
- While selecting the List Boxin excel, click on the developer tab and select the Properties. The Properties window will open.
- In the Properties window, for the List Fill Range setting, enter Days- the named range with the weekdays.
- Click in the Multi Select property, and from the drop-down list, select 1-fmMultiSelectMulti as shown in the below screenshot.
- Click in the List Style property, and from the drop-down list, select 1-fmListStyleOption. This adds checkboxes to the left of the list items as shown in the below screenshot.
- Close the Properties window.
You can close the Design mode off on clicking the Design Mode.
How to Create List Box in Excel VBA?
You can consider the following example for better understanding of VBA List Box :
To link this list box to a cell, right click on the list box (make sure design mode is selected) and click on Properties. Fill in B17 for Linked Cell as shown in the below screenshot.
Month List Name as Month go to properties and select List Fill range as Month.
Data will change in cell B17 cell when you change your choice in the list box as shown in the below screenshot.
Example 2 – How to Create a List Box on a Userform?
- Go To Developer Tab and then click Visual Basic from the Code or Press Alt+F11.
- Go To Insert Menu, Click UserForm.
- Drag List box Control on the Userform from the Toolbox.
- Double Click on the UserForm, and select the Userform event as shown in the below screenshot.
- Now, add the following code to the in-between procedure.
Private Sub UserForm_Initialize()
- Now, Press ‘F5’ to run the code.
Example #3 – How to add a dynamic list box control
Add a dynamic list box control on the userform using VBA
- Add a command button on the userform from the toolbox.
- Right click on the command button, click properties
- Change the command button caption to ‘Create_Listbox’
- Double click on the command button
- Now, it shows the following code.
Private Sub CommandButton1_Click()
- Call the below procedure named ‘Add_Dynamic_Listbox’ and find the below procedure to run.
‘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
- Now, click F5 to run the macro.
How to add items to list box control in VBA?
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?
Suppose we have a data of sales and we want the graphic user interface sales details by selecting list box item selection. Then we have to change select Index cell as cell link and apply the VLOOKUP formula to fetch the sales details as shown in the below screenshots.
Things to Remember About the List Box in Excel VBA
A list box in Excel can be used to select the multiple items from it.
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 –