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.
Download Practice Workbook
5 Ways to Use ListFillRange Property of ComboBox in Excel
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.
Read More: How to Use VBA to Populate ComboBox List from Range in Excel
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
Code Breakdown
- Here, we have declared Dbox as Object, and Darea as Range.
- Then, we assigned the range B14:C14 to the Darea It indicates the position of the drop-down box.
- With the help of the Dbox variable, we defined the position of this box.
- Finally, using the ListFillRange method we assigned the range starting from B5 to the last used cell of Column B.
- 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.
Read More: How to Use ComboBox to Get Selected Item Utilizing VBA
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.
='Dynamical range'!$B$5:INDEX('Dynamical range'!$B:$B, COUNTA('Dynamical range'!$B:$B)+2)
Here, the Dynamical range is the sheet name, and $B$5 is the start range.
Formula Breakdown
- COUNTA(‘Dynamical range’!$B:$B) → The COUNTA function returns the number of cells containing any text or numbers.
- Output →10
- COUNTA(‘Dynamical range’!$B:$B)+2 → becomes
- 10+2 → 2 is added because 2 cells are blank from the starting cell of Column B before the end of this column.
- Output → 12
- 10+2 → 2 is added because 2 cells are blank from the starting cell of Column B before the end of this column.
- INDEX(‘Dynamical range’!$B:$B, COUNTA(‘Dynamical range’!$B:$B)+2) → becomes
- INDEX(‘Dynamical range’!$B:$B,12) → The INDEX function returns the reference of the cell at the intersection point of Column B and Row 12.
- Output → $B$12
- INDEX(‘Dynamical range’!$B:$B,12) → The INDEX function returns the reference of the cell at the intersection point of Column B and Row 12.
- ‘Dynamical range’!$B$5:INDEX(‘Dynamical range’!$B:$B,COUNTA(‘Dynamical range’!$B:$B)+2) → becomes
- ‘Dynamical range’!$B$5:$B$12 → returns the values of this range as output.
- 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 practise by yourself, we have created a Practice section on the right side of each sheet.
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.