Dynamic Combobox ListFillRange without VBA

Question

I have an Excel Document that I need to deploy without VBA to my coworkers. I have a Combobox in one of its sheets that I want to populate dynamically. The content of the combobox is the result of a data query from a different workbook, so the number of lines may vary.

I created a temporary sub just to asign the ListFillRange. I have added all different attempts that I tried so far without success...

Sub SetListFillRange()
    ComboBox1.ListFillRange = "=MyQueryTable[SomeColumn]"
    ComboBox1.ListFillRange = "=TestName" 'A named Range pointing to MyQueryTable[SomeColumn]
    ComboBox1.ListFillRange = "=[TestName]"
    ComboBox1.ListFillRange = "='SheetX'!A2:INDEX('SheetX'!$A:$A,COUNTA('SheetX'!$A:$A))"
    ComboBox1.ListFillRange = "=OFFSET('SheetX'!A2,0,0,COUNTA('SheetX'!$A:$A) - 1,1)"
End Sub

The point why I'm trying to use the ActiveX ComboBox is the autocomplete feature which is missing in the form ComboBox that comes with Excel (in fact you may not type text into it at all...).

SJR SJR · Accepted Answer · 2020-04-01T15:48:55

If you're using an ordinary Excel table you could use List instead

Sub SetListFillRange()
    ComboBox1.List = ActiveSheet.ListObjects("Table1").ListColumns("SomeColumn").DataBodyRange.Value
End Sub

If you want to use ListFillRange you could try

ComboBox1.ListFillRange = ActiveSheet.ListObjects("Table1").ListColumns("SomeColumn").DataBodyRange.Address

Dynamic Combobox ListFillRange without VBA

1 Answers