Suggestions or help trying to resolve some activeX combobox issues?

I have been working on trying to resolve issues using ActiveX combo boxes. In the past I have used Data Validation referencing a drop down list. The problem I have with this is that when selecting the dropdown list, one must scroll up or down through the list to select the desired entry. This is an issue when there are up to 100 items in the list. What I have been trying to do, is create a way so that as the user begins to type in a selection, the list automatically shows items in the list that starts with those characters and errors if what the user is typing is not in the list. For this reason I have been playing with ActiveX combo boxes.

If anyone knows of a similar solution using the data validation method, that I am open to suggestions.

I am attaching a workbook that I have been playing around with that contains the worksheet with some entries and formulas as well as macros that I have been using.

I initially found this link:

"https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/"

This gave me the idea of where to start and it works great for One ActiveX combo box, but not necessarily for upwards of 40 that I may have in rows in my worksheet. I am sure there is someway to modify the formulas in column F, see F1, to make this work for the additional combo boxes. Initially in cell C1 there is an ActiveX combo box that aligns with the list in column E, and formulas, in column, F, G and H. The linked cell is B3 for this combobox1, B4 for combobox2 in cell C4, and B5 for combobox3 in C5

So for testing if you begin type in the combobox1 a C, it will give you three choices to choose from, Cash, Check, or Credit. If you continue to type CR, then it will only give you the choice of Credit, but should you type CRA, there is no match in the list in column E and if you click out of it, it will automatically clear the entry you made and give you a warning that you must choose an item from the list.

Any ideas are very much appreciated.

Thanks in advance.

I created a searchable combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type keyword in the combobox and the list will be narrowed down as you type.
So you only need 1 combobox for multiple cells.
The code makes the combobox list dynamic, sorted, & unique.
Here's an example:

Check sheet INFO on how to set it up.

Here is a way to do it without VBA, just using a variable length drop drown,

=IF(ISERROR(VLOOKUP(ROWS($F$2:F2),$C$2:$D$20,2,0)),"",VLOOKUP(ROWS($F$2:F2),$C$2:$D$20,2,0))

See attached.

Thanks Akuini, I have downloaded this and will study it. If I am correct and what I like about it, is that it only uses ONE combobox regardless of which line you are on. Is that correct?

Thanks Croweater, I try this out.

Thank You Akuini, This solution works great. I love the idea of not having to create multiple conboboxes... I have modified the code slightly to allow to use the solution in two columns. I have attached the modified version showing two columns in sheet2, with minor changes to the Option Explicit settings and the worksheetselectionchange, as well as the toshowcombobox.

Thank you so much, this is a wonderful solution.

Thank You Akuini, This solution works great. I love the idea of not having to create multiple conboboxes... I have modified the code slightly to allow to use the solution in two columns. I have attached the modified version showing two columns in sheet2, with minor changes to the Option Explicit settings and the worksheetselectionchange, as well as the toshowcombobox.

Thank you so much, this is a wonderful solution. I failed to reply directly to you, so I am doing this again.

Sorry, for the late reply.
I've checked your modification, it's ok. But because both columns have the same list then it's simpler to just change this part (in the original example):

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

In case you need multiple columns with different list for each column then you can check this example:

Thank you. The wonderful thing about this forum is that I learn something new every time I am here!

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

Keeping with forum rules, I have created a new tread since I already marked the previous one related to this topic as RESOLVED. The new topic is titled: "ActiveX combobox dropdown list not displayed when used in multiple columns".