I have been using the readily accessible "Double Click" VBA code with Data Validation/ComboBox in order to implement autocomplete for years. During a revamp of our spreadsheet, the data range for the data validation list is now contained in a table. In order to get the range reference to work, I used the indirect command to reference the table. Table is named "Equipment" and the column I want to reference is "Model."
This works fine and will allow me to scroll through all items in the table.
My issue is with the ComboBox/VBA autocomplete. In the properties of the ComboBox, I am not able to get the ListFillRange property to maintain a reference to the table.
It doens't allow me to enter the indirect reference INDIRECT("Equipment[Model]") into that field. I also tried using a named range that I set up in the name manager, model=INDIRECT("Equipment[Model]". This allowed me to enter the named range into the ListFillRange but this only works once before the parameter becomes empty again.
Has anyone had any luck getting the VBA ComboBox autocomplete approach to work with indirect table references? The table in our workbook is always changing size so a fixed range would not work.
This works fine and will allow me to scroll through all items in the table.
My issue is with the ComboBox/VBA autocomplete. In the properties of the ComboBox, I am not able to get the ListFillRange property to maintain a reference to the table.
It doens't allow me to enter the indirect reference INDIRECT("Equipment[Model]") into that field. I also tried using a named range that I set up in the name manager, model=INDIRECT("Equipment[Model]". This allowed me to enter the named range into the ListFillRange but this only works once before the parameter becomes empty again.
Has anyone had any luck getting the VBA ComboBox autocomplete approach to work with indirect table references? The table in our workbook is always changing size so a fixed range would not work.