Data Validation Autocomplete using VBA with Tables

[JoeSchuch]

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.

 

[Akuini]

Has anyone had any luck getting the VBA ComboBox autocomplete approach to work with indirect table references?

Are you talking about autocomplete combobox and also dependent data validation?

This allowed me to enter the named range into the ListFillRange but this only works once before the parameter becomes empty again.

Try using .List property instead of ListFillRange.
Example:

Private Sub ComboBox1_GotFocus()
ComboBox1.List = ActiveSheet.ListObjects("Table1").ListColumns("model").DataBodyRange.Value  'change all references to suit
End Sub

Note:
It will populate the combobox everytime you enter the combobox.
ListFillRange must be blank.

And in case you are interested.
I created an Excel add-in for searchable data validation (with combobox) , called "Search deList", and I'm sharing it as freeware.
Its function is to speed up the search in the data validation list. It works on multiple cells. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list.
By using this add-in, you don't need VBA to have this searchable combobox, so you can save your files as .xlsx.

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

 

[JoeSchuch]

Are you talking about autocomplete combobox and also dependent data validation?

Yes, this is what is not working for me.

Try using .List property instead of ListFillRange.
Example:

Private Sub ComboBox1_GotFocus()
ComboBox1.List = ActiveSheet.ListObjects("Table1").ListColumns("model").DataBodyRange.Value  'change all references to suit
End Sub

Note:
It will populate the combobox everytime you enter the combobox.
ListFillRange must be blank.

And in case you are interested.
I created an Excel add-in for searchable data validation (with combobox) , called "Search deList", and I'm sharing it as freeware.
Its function is to speed up the search in the data validation list. It works on multiple cells. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list.
By using this add-in, you don't need VBA to have this searchable combobox, so you can save your files as .xlsx.

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

Are you talking about autocomplete combobox and also dependent data validation?

Try using .List property instead of ListFillRange.
Example:

Private Sub ComboBox1_GotFocus()
ComboBox1.List = ActiveSheet.ListObjects("Table1").ListColumns("model").DataBodyRange.Value  'change all references to suit
End Sub

Note:
It will populate the combobox everytime you enter the combobox.
ListFillRange must be blank.

And in case you are interested.
I created an Excel add-in for searchable data validation (with combobox) , called "Search deList", and I'm sharing it as freeware.
Its function is to speed up the search in the data validation list. It works on multiple cells. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list.
By using this add-in, you don't need VBA to have this searchable combobox, so you can save your files as .xlsx.

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

Thank you for the response. I used your add-in and it is working well. I also added the double-click VBA code but it doesn't appear to work. I have tried placing the VBA code in ThisWorkbook that shows in my workbook and also tried in the Search_deList ThisWorkbook section. Not sure if you have run into this before.

 

[Akuini]

I also added the double-click VBA code but it doesn't appear to work. I have tried placing the VBA code in ThisWorkbook that shows in my workbook

After doing that you should save, close & reopen the workbook..

and also tried in the Search_deList ThisWorkbook section

NO, don't put it in the add-in!

 

[JoeSchuch]

After doing that you should save, close & reopen the workbook..

NO, don't put it in the add-in!

Thank you. Everything is working now.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.