VBA ListBox style

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 how to hold values in the list box in Excel VBA.

List Box is a box which can hold values in it. From this list of items, the user can select the item shown in the list box.

We can also draw a list box in a regular excel sheet as well.

How to Create List Box in Excel?

Assume you have a list of the month in one of the excel sheet.

For this list of months, we will create a list box. Follow the below steps to create the List Box in excel worksheet:

Step 1: Go to Developer Tab. Under developer, tab Go to Insert option, and under 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 month name range and give the cell reference to cell D1 and thenclick on Ok.

Step 5: We will have a list of the month in the list box.

Step 6: Select any of the items in the list box and see the change in cell D1 .

So, we got 3 as a result in cell D1 because Mar is the third item in 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 for a better understanding of the VBA List Box.

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: As soon as you select the line style, you can see the impact immediately.

Step 10: Run this code; you will see a list box in separately.

Step 11: Now, we will add 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 will store the data in cell G5. In order 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: Now, if you run the code and select any of the months, we can see the value selected by the user in the G5 cell.

I have selected Jun, so 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 the 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, initialize write the below code 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

• If you are directly giving cell reference, then you need to specify the sheet name and cell reference. [You need to add exclamation [!] symbol in front of 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, then 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 in VBA along with practical examples and a downloadable excel template. You can also go through our other suggested articles

1. VBA Replace
2. VBA ByVal
3. VBA Arrays
4. VBA XML