How to Use ListFillRange Property of ComboBox in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel combobox listfillrange

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.

excel combobox listfillrange for a range of cells

Step-01:

First, we will create a ComboBox.

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

combo

Then, a plus symbol will appear.

  • Drag down and to the right the plus symbol.

plus

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

excel combobox listfillrange for a range of cells

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.

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.

excel combobox listfillrange for a range of cells

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

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.

excel combobox listfillrange for a range of cells

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.

column count

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.

result for excel combobox listfillrange application

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.

Defining a Named Range in ListFillRange Property of ComboBox in Excel

Steps:

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

combobox1

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

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.

Defining a Named Range in ListFillRange Property of ComboBox in Excel

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.

lemon

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

Defining a Named Range in ListFillRange Property of ComboBox 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.

Applying Table for Excel ComboBox ListFillRange

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.

double click

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

code window

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

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

double-click

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

Applying Table for Excel ComboBox ListFillRange

  • 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.

Applying Table for Excel ComboBox ListFillRange

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.

Applying Table for Excel ComboBox ListFillRange


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.

Utilizing VBA Code for Excel ComboBox ListFillRange

Steps:

  • Go to the Developer tab >> Visual Basic.

Then, the Visual Basic Editor window will open up.

  • Go to the Insert tab >> Module.

VBE

In this way, we have created Module 1.

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.

Utilizing VBA Code for Excel ComboBox ListFillRange

  • 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.

Utilizing VBA Code for Excel ComboBox ListFillRange


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.

Setting Dynamic Range for Excel ComboBox ListFillRange

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
  • 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
  • ‘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.

formula

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

combobox

  • 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.

properties

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

Setting Dynamic Range for Excel ComboBox ListFillRange

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.

Setting Dynamic Range for Excel ComboBox ListFillRange


Practice Section

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

practice


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo