Data Validation Autocomplete using VBA with Tables

JoeSchuch

New Member
Joined
Jul 29, 2015
Messages
4
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."

1658861896975.png


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.
1658862185479.png


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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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:
VBA Code:
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.
 
Upvote 0
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:
VBA Code:
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.

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

Try using .List property instead of ListFillRange.
Example:
VBA Code:
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.

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.
 
Upvote 0
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!
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top