Dynamic Ranges With Active X Combo Boxes

[excelbytes]

I have an Active X Combo Box and I want the list reference to be dynamic, but it won't allow tables for the ListFillRange. Is there a way to have a dynamic list without using a named range that exceeds the existing list that allows for additions, but generates a lot of blanks at the bottom?

 

[Yongle]

Is this what you want

Private Sub ComboBox1_DropButtonClick()
    Me.ComboBox1.List = Sheets("Sheet2").ListObjects("Table1").ListColumns(1).DataBodyRange.Value
End Sub

 

[excelbytes]

I'm not sure. I wasn't necessarily anticipating a VBA solution, but that's great if I can get it to work. I entered the code you suggested, adjusting for my specific workbook:

Private Sub ComboBox1_DropButtonClick()
Me.ComboBox1.List = Sheets("Data").ListObjects("Table3").ListColumns(1).DataBodyRange.Value
End Sub

But nothing is showing up in the combo box drop down list. Do I need to enter anything in the ListFillRange box of the Properties for the ComboBox1 for it to work?

 

[Yongle]

But nothing is showing up in the combo box drop down list. Do I need to enter anything in the ListFillRange box of the Properties for the ComboBox1 for it to work?

No - the code provided should do everything
Now we need to work out what is wrong - it will probably be something minor

Where did you put the code?

 

[excelbytes]

See the attached.

 

[Yongle]

Place it in the sheet code window of the sheet containing the combobox
Right-click on the sheet tab \ View Code \ paste the code into that window

 

[excelbytes]

I have two combo Boxes on the same worksheet. I did as you suggested and now I get the attached error.

 

[Yongle]

You did not explain which one of your 2 macros failed

Was it ComboBox2_DropButtonClick ?

Me.ComboBox1.List = Sheets("Data").ListObjects("Table2").ListColumns(1).DataBodyRange.Value
Me.ComboBox2.List = Sheets("Data").ListObjects("Table2").ListColumns(1).DataBodyRange.Value

 

[excelbytes]

Both cause the same error to occur.

 

[Yongle]

I do not get an error. I need to understand your setup to try to recreate your error

What are the names of your 2 tables?
Are they both on sheet named "Data" ?
Are they single-column tables ?