Help with VBA code for ActivX combo for partial string search and item selection

Hi All

This is my 1st post and I am just beginning with VBA code. I have set up an ActiveX combo box linked to an item list range "CATS" (expenditure categories) and the code at present does autocomplete using the initial text entry in the combo box. I would like to change this to a dynamic text string search which will filter the drop down display list to input string matches (any position) from the items in "CATS". I then use the down arrow and then ENTER to make the selection as well as moving down to the next row for the next selection process.

The code I have presently is;

Please Login or Register  to view this content.

Perhaps someone has written a code package that uses partial string matching in an ActiveX combo?

The code I have presently has been taken from an online sample somewhere and I have tinkered with it a little - it works just fine for autocomplete list selections.

I would really appreciate any help forum members can offer as I am stuck on this one.

Cheers from an Aussie.

Hi,AMJP_aussie. Welcome to the Forum.
Try this:
How it works:
- The cells in the blue area (col B,D,E) has data validation (with List type)
- Selecting a cell in the blue area will activate the combobox
- Type some keywords to search, separated by a space, e.g "f ca"
- The result will be narrowed down as you type.
- The search ignores the keywords order, so the keyword "ma la" would match "Maryland" and "Alabama".
- use up-down arrow to select an item then hit ENTER, the selected item will be inserted into the cell.
- to leave the combobox: hit TAB or ESC
- to delete 1 cell content: select the cell then hit ENTER (while combobox is empty)
- to delete more than 1 cell content: select the cells (it won't activate the combobox) then delete
Note: selecting more than 1 cell won't activate the combobox
- In the Status Bar you can see how many unique items are found & displayed.

ON-OFF BUTTON:
You can turn the combobox on and off. So if you don’t want the combobox set up to be activated then just click the ON-OFF button.


Example:

Hi Akuini

Thanks very much for your code for my ActiveX combo box partial string search and select.

I have tested it out and it works brilliantly.

Only part I do not understand is why the active cell goes one column off to the right after item selection?

I need the active cell after selection to be the cell below.

Another idea how about a status marker for the ON/OFF control eg red shading when set to ON, green when OFF?

However just minor things - really appreciate your great code and quick response. Guess it helps being in your time zone.

Cheers JP_aussie

Only part I do not understand is why the active cell goes one column off to the right after item selection?

Change this part in Sub toShowCombobox():

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I need the active cell after selection to be the cell below.

Set it like this:

Please Login or Register  to view this content.

Another idea how about a status marker for the ON/OFF control eg red shading when set to ON, green when OFF?

I changed Sub toOnOff(), you can change it to suit your needs.

Please Login or Register  to view this content.

Hi Akuini

Thanks for those code mods.

All good apart from cannot implement the active cell to be one down after combo item select.

I am attaching the current TEST SS for you to see.

Thanks greatly

JP_aussie

Don't change the code in Private Sub ComboBox1_KeyDown, just change the value of ofs1 & ofs2 in this part, like this:

Please Login or Register  to view this content.

OK Akuini,

Fantastic. That did the trick.

I did not know that bit of code went at the very top of the tree.

Many thanks

JP_aussie

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