How to Use ListFillRange Property of ComboBox in Excel

If you are trying to use the ListFillRange property of ComboBox in Excel, then this article will be helpful for you. The ListFillRange property is useful for showing the values in a drop-down list of a ComboBox. So, let’s start with our main article to know more about the usage of this property.

How to Use ListFillRange Property of ComboBox in Excel: 5 Ways

We will work with the following dataset containing a list of products with their corresponding regions and sales values. For creating a drop-down list in a ComboBox, we can use the ListFillRange property as stated in the following 5 examples.

For creating this article, we have used Microsoft Excel 365 version. However, you can use any other version at your convenience.

Example-1: Using a Cell Range in ListFillRange Property

Here, we will create a drop-down list containing the list of products in a ComboBox and the range of cells B5:B12 will be used as ListFillRange.

Step-01:

First, we will create a ComboBox.

• Go to the Developer tab >> Insert group >> Combo Box (ActiveX Control).

Then, a plus symbol will appear.

• Drag down and to the right the plus symbol.

In this way, we have created a combo box, and the name of the combo box is ComboBox1.

Step-02:

Now, we will give input our range into the combo box drop-down list.

• Right-click on the created box and select the option Properties.

Then, you will see the Properties window on the left part.

• Type the range B5:B12 in the ListFillRange box.
• Press ENTER and close the window.

• Now, we will turn off the design mode by going to the Developer tab >> clicking on Design Mode.

Then, if you click on the drop-down symbol of this combo box, then you will see the list of products.

• Here, we are choosing Mango from this list, you can choose any option.

Later, the product Mango will appear in the box.

However, if you want to show the values from two columns; Fruit, and Region, in the drop-down list, then you can follow this trick.

• In the Properties window, choose 2 as ColumnCount, and the range limit of the ListFillRange is extended to B5:C12.

Here, you can see the fruits and their corresponding region names in the dropdown list. But only the first portion means the name of the fruit will appear in the box.

• Select any option from the list (here, we are selecting Kiwi).

Later, only Kiwi will appear in the box.

Read More: How to Create a Searchable ComboBox with VBA in Excel

Example-2: Defining a Named Range in ListFillRange Property of ComboBox in Excel

Instead of defining a range of cells directly, here, we will use a name for this range. For example, we can see the name of the fruits in range B5:B12 is Fruits, so we can use this name for declaring this range.

Steps:

• Follow Step-01 of Example-1 to create the following ComboBox1.

• Disable the design mode, right-click on this box, and click on Properties.

Then, you will see the Properties window on the left part.

• Type the range name Fruits in the ListFillRange box.
• Press ENTER and close the window.

Then, if you click on the drop-down symbol of this combo box, then you will see the list of products.

• Here, we are choosing Lemon from this list, you can choose any option.

Finally, the fruit name Lemon will appear in the box.

Example-3: Applying Table Name to Update Drop-Down List of a ComboBox

Here, we have a table named Table1, and we will try to put this name for the ListFillRange property of the ComboBox. But, unfortunately, this property will not take the table name directly. So, we have to take a little help from a VBA code.

Steps:

• Follow Step-01 of Example-1 to create the following ComboBox1.

• Double-click on this box to open up the code window for it.

Later, the code window will open up.
Here, we can see the Change event for the ComboBox1 object.

But, we want to replace it with a double-click event.

• Click on the drop-down symbol of the procedure portion.
• Select DblClick.

Later, we will get the following sub-procedure name for this event.

• So, here is the full code required for giving input the table name for the list range.

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Drng = Range("Table1").Address(0, 0)
Me.ComboBox1.ListFillRange = Drng
End Sub

• Return to the main sheet.
• Turn off the design mode of the combo box, and double-click to activate the code.

Then, if you click on the drop-down symbol, then the list of fruit names will appear.

Moreover, if you add another row for Starfruit, then immediately it will also appear on the drop-down list of the combo box. And it is the advantage of using a table.

• Click on Starfruit.

As a result, we will have Starfruit in the combo box.

Example-4: Utilizing a VBA Code to Insert a ComboBox with a List of Ranges

In this section, we will use a VBA code to insert a ComboBox in our desired position with our defined range.

Steps:

• Go to the Developer tab >> Visual Basic.

Then, the Visual Basic Editor window will open up.

• Go to the Insert tab >> Module.

In this way, we have created Module 1.

• Type the following code in your newly created module.

Sub list_Dcomb()
Dim Dbox As Object
Dim Darea As Range
Set Darea = Range("B14:C14")
Set Dbox = ActiveSheet.DropDowns.Add(Darea.Left, Darea.Top, _
Darea.Width, Darea.Height)
Dbox.ListFillRange = "B5:B" & Range("B" & Rows.Count).End(xlUp).Row
End Sub

• Press F5.

Then, you will have your newly created ComboBox within the range of B14:C14.

• Select any option from this list (we selected Avocado).

In this way, we put down Avocado in the combo box.

Example-5: Dynamically Setting a List Range for a ComboBox

In this section, we will create a dynamic named range so that it will be always updated for any kind of changes. Then, we will use this range as a drop-down list for our combo box.

Steps:

• Go to Formulas tab >> Name Manager.

Afterward, the Name Manager dialog box will appear.

• Click on New.

Then, another window will appear.

• Choose any name, here we used Fruit1 in the Name box.
• Put down the following formula in the Refers to box.

Here, the Dynamical range is the sheet name, and $B$5 is the start range.

• Follow Step-01 of Example-1 to create the following ComboBox1.

• Disable the design mode, right-click on this box, and click on Properties.

Then, you will see the Properties window on the left part.

• Type the range name Fruit1 in the ListFillRange
• Press ENTER and close the window.

Then, if you click on the drop-down symbol, then the list of fruit names will appear.

Moreover, if you add another row for Starfruit, then immediately it will also appear on the drop-down list of the combo box. And it is the advantage of using a table.

• Click on Starfruit.

Finally, your selected fruit name will appear inside the ComboBox.

Practice Section

To practice by yourself, we have created a Practice section on the right side of each sheet.

Download Practice Workbook

Conclusion

In this article, we have discussed different ways to use the ListFillRange property of ComboBox in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.

Related Articles

• How to Select First Item from ComboBox Using VBA in Excel
• How to Use ComboBox to Get Selected Item Utilizing VBA
• Insert ComboBox with Listindex in Excel VBA
• How to Clear Items from VBA ComboBox in Excel
• How to Reset ComboBox to Default Value Using VBA