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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
ComboBoxes on forms attempt to guess what you are typing as you type. With 90,000 entries I would use AdvancedFilter on the sheet to produce a separate list that would then populate the ComboBox.

I can provide some sample code if you want
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)
mikexmlAuthor Commented:
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.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

mikexmlAuthor Commented:
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.
Roy CoxGroup Finance ManagerCommented:
The list has no unique examples, so I guess that you want a part match

Try this, I've added a TextBox to enter the search term, e.g. zuck. On exiting the combobox will populate with all entries starting with zuck

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
You would also need to reset the MatchRequired property to False. See example
mikexmlAuthor Commented:
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?
Roy CoxGroup Finance ManagerCommented:
Sorry, I meant to change that.  Try it and if you have problems I'll help you.
mikexmlAuthor Commented:
Spot on Roy thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.