Updated June 12, 2023
What is List Box in Excel VBA?
List Box is one of the tools under user form in VBA. As the name itself suggests, list box will hold all the list of values given by the user. In VBA List box comes with a user form. In this article, we will see how to create a list box and hold values in the list box in Excel VBA.
A list Box is a box that can hold values in it. The user can select the item shown in the list box from this list of items.
We can also draw a list box in a regular Excel sheet.
How to Create List Box in Excel?
Assume you have a list of the month in one of the Excel sheets.
For this list of months, we will create a list box. Follow the below steps to create the List Box in the Excel worksheet:
Step 1: Go to Developer Tab. Under the developer tab, Go to the Insert option; under the insert option, Go to Form Control; under form control, select List Box.
Step 2: After selecting the list box, draw the list box in the Excel sheet.
Step 3: Right-click on the inserted list box and select Format Control.
Step 4: Under Format Control, go to Control. Select the input range as the month name range, give the cell reference to cell D1, and then click Ok.
Step 5: We will have a list of the month in the list box.
Step 6: Select any items in the list box and see the change in cell D1.
So, we got 3 in cell D1 because Mar is the third item on the list.
How to Create List Box in VBA?
Create a list box is not that easy; as we have seen in the above example, you need to have a considerable amount of knowledge of Excel VBA Macros.
VBA List Box – Example #1
Understanding of VBA List Box.
You can consider the following example to understand the VBA List Box better.
Follow the below steps to create a list box in VBA.
Step 1: In the Visual Basic window, Insert the User form.
Step 2: As soon as you insert the user form, you can see the toolbox along with the user form.
Step 3: In this toolbox, select List Box.
Step 4: Draw this list box on the user form.
Step 5: After inserting the list box, give a proper name to this list box under the Properties window.
Step 6: Scroll down and find “Row Source.” In this, type the range of values of your month name along with the sheet name.
Step 7: As soon as you give the reference, you can see the month names in the list box.
Step 8: Now, in the list box properties window, select line style as “1 – fmListStyleOption“.
Step 9: You can see the impact immediately when you select the line style.
Step 10: Run this code; you will see a list box separately.
Step 11: Now, we will add a macro code to store the data selected by the user. Double-click on the list box. You will see a separate macro name like the one below.
Private Sub Month_List_Box_Click() End Sub
Step 11: When the user selects the month, we store the data in cell G5. To store the input given by the user, add the below code.
Private Sub Month_List_Box_Click() Range("G5").Value = Month_List_Box.Value End Sub
Step 12: If you run the code and select any of the months, we can see the value the user selects in the G5 cell.
I have selected Jun so that I can see Jun in the G5 cell. Like this, we can use a list box in your VBA projects to take input from the users.
VBA List Box – Example #2
Add.Items Method to Add Values to List Box
Follow the below steps to create a list box in VBA.
Step 1: Create a New UserForm. Here it is, UserForm2.
Step 2: Add List box in Userform 2.
Step 3: After inserting the list box, give a proper name to this list box under the Properties window, i.e., Month_list_Box1.
Step 4: Now, in the list box properties window, select line style as “1 – fmListStyleOption“.
Step 5: Double-click on the user form. You will see an auto-inserted macro like the below one.
Private Sub UserForm_Click() End Sub
Step 6: By placing a cursor inside the macro, select “Initialize.”
Step 7: As soon as you selected “Initialize”, we can see a new macro to be created just below the current one.
Private Sub UserForm_Initialize() End Sub
Step 8: Delete the old macro. Under UserForm, write the code below to add items to the list box.
Private Sub UserForm_Initialize() Month_List_Box1.AddItem "Jan" Month_List_Box1.AddItem "Feb" Month_List_Box1.AddItem "Mar" Month_List_Box1.AddItem "Apr" Month_List_Box1.AddItem "May" Month_List_Box1.AddItem "Jun" Month_List_Box1.AddItem "Jul" Month_List_Box1.AddItem "Aug" Month_List_Box1.AddItem "Sep" Month_List_Box1.AddItem "oct" Month_List_Box1.AddItem "Nov" Month_List_Box1.AddItem "Dec" End Sub
Step 10: Now press the F5 button to run the code, and you will get the following output.
Things to Remember
- You need to specify the sheet name and cell reference if you give cell references directly. (You need to add an exclamation (!) symbol in front of the sheet name).
- You can also add items to the list box by using Add.Item method.
- If you want to allow the user to select more than one item, you need to select the “fmMultiSelectMult” option under Multi-Select in the Properties window.
Recommended Articles
This is a guide to VBA List Box. Here we discuss how to Create a List Box in Excel and VBA, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
