How to Use ListFillRange Property of ComboBox in Excel (5 Methods)

In this guide, we’ll work with a dataset containing a list of products, their corresponding regions, and sales values. We’ll explore five methods for creating drop-down lists in a ComboBox using the ListFillRange property.excel combobox listfillrange


Method 1- Using a Cell Range:

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

excel combobox listfillrange for a range of cells

Step 1 – Create a ComboBox:

  • Go to the Developer tab.
  • In the Insert group, select ComboBox (ActiveX Control).

combo

  • A plus symbol will appear; drag it down and to the right to create the ComboBox.

plus

The name of the ComboBox is ComboBox1.

excel combobox listfillrange for a range of cells

Step 2- Input the Range:

  • Right-click on the ComboBox and choose Properties.

properties

  • In the Properties window, enter the range B5:B12 in the ListFillRange box.
  • Press ENTER and close the window.

excel combobox listfillrange for a range of cells

  • Turn off design mode by clicking Design Mode in the Developer tab.

design mode

  • Click the drop-down symbol to see the list of products. Select Mango or any other option.

Later, the product Mango will appear in the box.

excel combobox listfillrange for a range of cells

Step 3 – Display Values from Two Columns:

  • To show both fruit names and corresponding regions:
    • Set ColumnCount to 2 in the Properties window.
    • Extend the ListFillRange to B5:C12.

column count

  • Now the dropdown list displays fruit names and regions, but only the fruit name appears in the box.

  • Select an option (e.g., Kiwi), and 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


Method 2 – Defining a Named Range:

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:

  • Create ComboBox1 (Follow Step 1 from Example 1).

combobox1

  • Define a Named Range:
    • Disable design mode.
    • Right-click on ComboBox1 and select Properties.

properties

    • In the Properties window, 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

    • Click the drop-down symbol to see the list of products. Choose Lemon or any other option.

lemon

    • The fruit name Lemon will appear in the box.

Defining a Named Range in ListFillRange Property of ComboBox in Excel


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

  • Table Setup:
    • Assume we have a table named “Table1.”
    • Our goal is to use this table name for the ListFillRange property of the ComboBox.
    • Unfortunately, this property doesn’t directly accept table names, so we’ll need to use a VBA code.

Applying Table for Excel ComboBox ListFillRange

  • Steps:
    • Follow Step 1 from Method 1 to create ComboBox1.

    • Double-click on the ComboBox to open the code window.

double click

    • Replace the existing Change event with a double-click event:

code window

      • Click the drop-down symbol next to the procedure portion.
      • Select DblClick.

double-click

    • Use the following code to 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 design mode for the combo box and double-click to activate the code.

    • Click the drop-down symbol to see the list of fruit names.

Applying Table for Excel ComboBox ListFillRange

    • If you add another row (e.g., Starfruit), it will immediately appear in the combo box due to the advantage of using a table.
    • Click on Starfruit.

    • We will have Starfruit in the combo box.

Applying Table for Excel ComboBox ListFillRange


Method 4 – Utilizing VBA Code to Insert a ComboBox with a Defined Ranges:

Utilizing VBA Code for Excel ComboBox ListFillRange

Steps:

  • Go to the Developer tab >> Visual Basic.

  • In the Visual Basic Editor window, go to the Insert tab >> Module.

VBE

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 to execute the code.
  • You’ll have a new ComboBox within the range of B14:C14.

  • Select an option from the list (e.g., Avocado) to populate the combo box.

Utilizing VBA Code for Excel ComboBox ListFillRange


Method 5 – Dynamically Setting a List Range for a ComboBox:

In this section, we’ll create a dynamic named range that automatically updates with any changes. We’ll then use this range as a drop-down list for our combo box.

Setting Dynamic Range for Excel ComboBox ListFillRange

Steps:

  • Go to the Formulas tab and click on Name Manager.

  • In the Name Manager dialog box, click New.

 

  • In the new window, choose any name (e.g., Fruit1) for the Name box.
  • Enter 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) → returns the number of cells containing text or numbers (e.g., 10).
  • COUNTA(‘Dynamical range’!$B:$B)+2 →adds 2 because there are 2 blank cells before the end of Column B (e.g., 12).
  • INDEX(‘Dynamical range’!$B:$B, COUNTA(‘Dynamical range’!$B:$B)+2) → returns the reference of the cell at the intersection of Column B and Row 12 (e.g., $B$12).
  • ‘Dynamical range’!$B$5:INDEX(‘Dynamical range’!$B:$B,COUNTA(‘Dynamical range’!$B:$B)+2) →represents the values within this range.

formula

  • Follow Step 1 from Method 1 to create ComboBox1.

combobox

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

  • In the Properties window, type the range name Fruit1 in the ListFillRange box.
  • Press ENTER and close the window.

properties

  • Click the drop-down symbol, and the list of fruit names will appear.

Setting Dynamic Range for Excel ComboBox ListFillRange

  • If you add another row (e.g., Starfruit), it will immediately appear in the combo box’s drop-down list due to the advantage of using a table.

  • Click on Starfruit, and your selected fruit name will appear inside the ComboBox.

Setting Dynamic Range for Excel ComboBox ListFillRange


Practice Section

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

practice


Download Practice Workbook

You can download the practice workbook from here:


Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo