VBA List Box

What Is List Box In Excel VBA?

VBA List Box in Excel is a list assigned to a variable. The VBA List Box contains the list of items given by a user which allows other users to select the desired values from the list box as part of the automation of reports in Excel.

For example, if we want a user to select months from a list, we can create a list box in Excel with the help of VBA code and get the input values from the users. This article will be a complete guide for VBA list box.

How To Create A List Box In VBA?

The list box is available under the Developer tab in Excel.

There are two kinds of VBA list boxes available i.e., Form Controls List Box and ActiveX Controls List Box. Since we are talking about the VBA list box, we will talk only about the ActiveX Controls list box.

Step 1: To create a list box, first, go to the Developer tab.

Next, under the Insert option, click on ActiveX Controls List Box.

Step 2: Once we click on the list box, we must draw this on our worksheet.

As we can see, we have got a rectangle box. Now, to get values on the list box, we need to design the list box.

Step 3: Next, right-click on the list box and then, click on Properties.

This will bring the properties window as shown in the following image:

We will not be using all the properties. However, we will take you through some of the important properties of the list box.

Name – In this property, we can give a name to the VBA list box. Now, give a name that we can easily access in the list box.

Once we give a name to the list box, we will see the same name in the name box in excel whenever the list box is selected.

Back Color – In this property, we can change the background color of the list box.

Next, click on the drop-down and then, choose the background color as per our wish.

Border Color and Border Style – Using this property, we can change the border color of the list box and the style.

Font – Here, we can give different font names.

List Fill Range – Here, we can give the values to be displayed in the list box. For example, we have the day names in cells A1 to A7.

To get these values on the list box, we can give this cell range address under the list fill range.

As soon as we give the input range, we will get the list of items on the list box as shown in the below image:

Multi Select – Here, we can allow the user to select a single value or multiple values.

List Style – Here, we can design the list of items. Here we can select either plain or style. With plain, we get the default list of box items.

Next, choose the style option, and we will get the following list box style.

This is the way of creating a list box in the worksheet.

Creating A List Box Using VBA

However, the process of creating a list box using VBA is slightly different.

Assume we have given only the name to the list box, and we have a blank list box like the following.

Step 1: To insert values in the list box, we can write a VBA code. Double-click on the list box, which will open the Visual Basic editor window with the following private sub-procedure auto-populated.

Step 2: To access the list box, we can enter the list box name that we have given under the Properties settings. Start typing the name My_List_Box and we can see the IntelliSense list showing this name.

Step 3: Next, choose the list box and then, enter a dot to see the properties and methods available with the list box.

Step 4: Since we are trying to add values to the list box, choose the AddItem method.

Now, enter the required value in double quotes.

Step 6: Let’s run this code, and we should see a given value on the list box.

Step 7: Now, to add multiple values, we need to write multiple lines of code. However, we can give the reference to the cells by using the List File Range property.

Step 8: Next, choose this property, enter an equal sign, and then, provide the cell address as A1:A7 in double-quotes.

Now, when we run this code, we will get the list of values on the list box from the excel cell reference A1:A7.

How To Create A List Box On A UserForm?

The VBA List Box is created on top of the user form in VBA; without creating a user form, we cannot access the list box.

Step 1: First, go to the Insert tab in the Visual Basic Editor window and then, click on User Form.

As soon as we click on the user form, it will insert a user form like the following along with the toolbox.

Now, from the toolbox, we can insert a list box on the user form.

Step 2: Next, click on the list box from the toolbox.

Step 3: After selecting the list box from the toolbox, draw this on the user form.

Step 4: Once the list box is inserted, we can access the properties of this list by pressing the F4 key.

This properties window is similar to the previous properties window. Now, we can play around with the properties to see the functionality of the list box.

Step 5: Let us show the VBA coding to play with the list box.

Next, double-click on the user form (not on the list box), and we will see the following auto-sub procedure.

This auto-initiated macro is used whenever the user form is clicked. We need to insert values into the list box.

Step 6: Next, copy the following code and then, paste it into the Visual Basic Editor window.  

Private Sub UserForm_Click()       

UserForm1.ListBox1.AddItem “Monday”

UserForm1.ListBox1.AddItem “Tuesday”   

UserForm1.ListBox1.AddItem “Wednesday”   

UserForm1.ListBox1.AddItem “Thursday”   

UserForm1.ListBox1.AddItem “Friday”   

UserForm1.ListBox1.AddItem “Saturday”   

UserForm1.ListBox1.AddItem “Sunday”

End Sub

The above code will insert the values to the list box whenever we click on the user form.

Step 7: When we run this code, it will bring us the blank user form and list box like the following.

To insert values on the list box, click on the user form. Then, we will see values inserted in the list box.

Alternative Code:

Instead of using the list box name to insert a value in every line of the code, we can use the WITH statement to use the user form and list box once and add the line of the item by line. The following code will insert the values into the list box.

Private Sub UserForm_Click()       

With UserForm1.ListBox1   

.AddItem “Monday”   

.AddItem “Tuesday”

.AddItem “Wednesday”

.AddItem “Thursday”

.AddItem “Friday”

.AddItem “Saturday”

.AddItem “Sunday”

End With

End Sub

Store the Value from the List Box

Whenever the user selects the value in the list box, we need to store that value somewhere in the worksheet.

Next, double-click on the list box, and we will see an auto-sub procedure.

Now, add the following code for the above procedure.

Private Sub ListBox1_Click()        

Range(“A1”).Value = UserForm1.ListBox1.Value

End Sub

Whenever the user selects something from the list box, it will store that value in cell A1. For example, let’s run this code, and we will see a blank list box like the following.

Next, click anywhere on the user form, and values will populate the list box.

Now, select any of the values from the list box, and the same value will be stored in cell A1.

We have selected the value Wednesday, and the same value is stored in cell A1.

How To Add A Dynamic Control To List Box?

The List Box was created manually in the user form in the above code. However, we can automate the process of inserting a list box by having a dynamic list box.

Step 1: First, create a blank user form like the following:

Step 2: Next, insert a command button on the user form from the toolbox.

Step 3: Now, right-click on the command button and then, click on Properties.

Step 4: This will open the property window. Give the caption as Insert List Box.

As we change the caption, we see the same name in the command button.

Step 5: Next, double-click on the command button, and we will see the following auto-initiate procedure.

Step 6: In this procedure, add the following code.

Sub Add_Dynamic_Listbox() 

‘Add Dynamic List Box and assign it to object ‘LstBx’ 

Dim LstBx As ListBox 

Set LstBx = UserForm3.Controls.Add(“Forms.ListBox.1”)’’

”List Box Position 

LstBx.Left = 20 

LstBx.Top = 10 

End Sub

How To Add Items To List Box Control In VBA?

To add items to the list box control, we can write a VBA code or make use of the properties window of the list box.

Step 1: First, enter the required values in the range of cells.

Step 2: In the Visual Basic Editor window, select the list box created on top of the user form. Next, press the properties shortcut key F4 to bring the following properties window.

Step 3: Next, scroll down in the properties window and in the row source, and give the cell address where we have values to be shifted to the list box i.e., range A1 to A7.

As soon as we provide the cell range, we can see the values appear in the list box.

By Using Named Range

Providing the range of cells is not a good practice; to make a list dynamic, we need to use a named range in excel.

Step 1: To begin with, select the range of cells, A1 to A7 and give it a name in the name box, “Week_Days”.

Step 2: Now, in the row source property of the list box, enter this named range name.

Using The VBA Code

Apart from the above two methods, we can write the VBA code to insert values into the list box.

To insert a value to the first, we need to access the list box by the name of the list box like the following.

After entering the list box name, enter the dot and access the add item method.

Next, enter the value in double quotes.

Similarly, write another line of code to insert another value.

Like this, we can add values to the list box by writing the code.

How To Clear Items From List Box Control Using VBA?

To clear items from the List Box, we need to either use the properties or write a VBA code. We can use the clear method of the list box in VBA to clear items from it.

This will clear the items from ListBox1.

The Constants Of List Box In VBA

In the multi-select property of the list box, we have 3 constant options, and they are:

• 0 – fmMultiSelectSingle
• 1 – fmMultiSelectMulti
• 2 – fmMultiSelectExtended

Let us explain these 3 constants in detail.

• 0 – fmMultiSelectSingle – If we choose this option, users can select only one item from the list box.
• 1 – fmMultiSelectMulti – If we use this option, we can allow the user to select multiple values from the list box.
• 2 – fmMultiSelectExtended – This is a special feature where we can allow the user to choose a value by using the SHIFT key and clicking on the mouse button or using the arrow key to select or deselect the values from the list box.

Important Things To Note

• When we draw the list box on the worksheet, we need to use the list fill range property to insert values. But when we use the list box on the user form, we need to use the row source property to insert values.
• In the list fill range or row source, we need to enter the cell address without double quotes.
• When we draw the ActiveX Controls List Box on the worksheet, we need to uncheck the design mode and then use the list box.
• We can change the background color of the VBA list box by using the background property of the list box.

Frequently Asked Questions

Download Template

This article must be helpful to understand the VBA list box, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA List Box. Here we explain how to create, add and clear list box in excel with the help of VBA code and downloadable excel template. You may learn more from the following articles –

• VBA Format
• VBA Operators
• VBA Case Statement