mikexml
asked on
VBA Excel Self filtering Combo Box
This might seem a very basic question, but it has me completely stumped and from my days in VB6, I was sure this was an automatic feature built into dropdown combo boxes, but in VBA excel (on a form, not sheet!) I am unable to do this.
The form has a single combobox, nothing else.
I have a single column of data in Excel with 90,000 commodities on it. I have manually created a range of all 90,000 items and set the range name of these to 'rCommodity'.
I run the form, the list populates with all 90,000 perfectly.
As I type into the combobox, I want the list to autofilter itself and contain only those results that contain (not just starting with) the entered text. Two ways I can do this I guess, filter the actual list in the worksheet and then copy the results back into the combobox as the new rowsource or find some way to filter the list in the items of the combobox itself.
My question is this - virtually every combobox today seems to have an autofilter built in that shortlists only those matches with the input text. Is there one of the properties of the excel vba combobox that filters this automatically? Am I missing something obvious or am I going to have to code this into the events of the combobox and filter the list and reload. In which case can someone suggest which event I should be using to perform this? Afterupdate, change, enter?? I have not been able to find a clear explanation of these events of a combobox
The form has a single combobox, nothing else.
I have a single column of data in Excel with 90,000 commodities on it. I have manually created a range of all 90,000 items and set the range name of these to 'rCommodity'.
I run the form, the list populates with all 90,000 perfectly.
As I type into the combobox, I want the list to autofilter itself and contain only those results that contain (not just starting with) the entered text. Two ways I can do this I guess, filter the actual list in the worksheet and then copy the results back into the combobox as the new rowsource or find some way to filter the list in the items of the combobox itself.
My question is this - virtually every combobox today seems to have an autofilter built in that shortlists only those matches with the input text. Is there one of the properties of the excel vba combobox that filters this automatically? Am I missing something obvious or am I going to have to code this into the events of the combobox and filter the list and reload. In which case can someone suggest which event I should be using to perform this? Afterupdate, change, enter?? I have not been able to find a clear explanation of these events of a combobox
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not sure the simple combobox control can handle this. Therefore, if you insist on using the combobox, only having everything in alphabetical order is the next best thing (so it doesn't filter anything INSIDE the string, only the start of the string)
ASKER
Yes I see that as I type into the combo, it half guesses (but usually by starting letter - I need all those matched results that contain the inputted text) and it finds the best single match which its creates as the combobox1.value which is no help at all. We just need the list to live filter itself or the underlying rowsource data and bring it back.
So I guess we need to keep creating a range list, via advanced filter on the worksheet, that revises itself as the user types into the combobox and then reloads the best fit items. My biggest problem has been which event to use of the combo as the user types because it constantly autoselects a single match and we get into a loop where no new data is filtered.
I am using a range as the row source, not via .additem.
Sample code would be perfect. Suppose we have a single column A of data on Sheet1, 8,000 rows long. The range is called rCommodity (which is A2:A90,001). Then a userform with comboBox1 on it.
The sample xlsm is attached and has the userform. I have just used 8000 items for file size.
EE-Sample-1.xlsm
So I guess we need to keep creating a range list, via advanced filter on the worksheet, that revises itself as the user types into the combobox and then reloads the best fit items. My biggest problem has been which event to use of the combo as the user types because it constantly autoselects a single match and we get into a loop where no new data is filtered.
I am using a range as the row source, not via .additem.
Sample code would be perfect. Suppose we have a single column A of data on Sheet1, 8,000 rows long. The range is called rCommodity (which is A2:A90,001). Then a userform with comboBox1 on it.
The sample xlsm is attached and has the userform. I have just used 8000 items for file size.
EE-Sample-1.xlsm
ASKER
It seems that the first important factor is to turn off the MatchedEntry Property by setting it to
2 - No matching. This stops the user input part of the combobox from being filled with the best match which keeps wrongly changing the .value and .text fields. What is the difference between these data types - they both seems to be plain text?
Now that MatchedEntry is off, we have an input box with data to filter the underlying data table.
2 - No matching. This stops the user input part of the combobox from being filled with the best match which keeps wrongly changing the .value and .text fields. What is the difference between these data types - they both seems to be plain text?
Now that MatchedEntry is off, we have an input box with data to filter the underlying data table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Roy, we are very close with Sample 1-2 however the keydown event is not the best because it occurs before the character is entered into the combobox input area. Keydown is therefore working on the previous combobox1.value and not the current one. For example if I type A followed by S then it is actually searching by the A only.
I think we need to use the change event?
I think we need to use the change event?
Sorry, I meant to change that. Try it and if you have problems I'll help you.
ASKER
Spot on Roy thanks