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?

 

[excelbytes]

Table1, Table2
Yes
Yes

See attached images

 

[Yongle]

Let's see if you can get the code working in a NEW workbook (which is what I am doing) and then we will try to work out what is different in your live workbook

We are both using Excel365 ... so what works for me should work for you ?

1 Rename sheet1 as "Data"
2 Add your values and create your 2 Tables (will be autonamed Table1 and Table2)
3 Insert a new sheet and on that sheet insert 2 active-x comboboxes (will be autonamed ComboBox1 and ComboBox2)
4 Paste code below into 2nd sheet's code window
5 Test by clicking on the drop button
6 That should work
7 Tell me how you get on

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

Private Sub ComboBox2_DropButtonClick()
Me.ComboBox2.List = Sheets("Data").ListObjects("Table2").ListColumns(1).DataBodyRange.Value
End Sub

 

[excelbytes]

I created a new workbook and followed your instructions and it worked perfectly. I went back to my current workbook, deleted all the current combo boxes and tables, rebuilt them and now all works perfectly. Not sure what happened, but thank you so much for your help!

 

[Yongle]

Yes - Excel works in mysterious ways at times