How to use a ComboBox with autocomplete and search as you type.

[dagda13]

Hi,

I have a ComboBox embedded in a worksheet that references a list of items. Ie:

small cat
cat
big cat
small dog
dog
big dog


Right now when I type in 'cat' i only get 'cat' as the suggestion. What I would like is to get all items that contain 'cat'., ie., in this case:

small cat
cat
big cat

Right now I have the "Match Entry" in the ComboBox Properties set to "1 - fmMatchEntryComplete", which allows me to filter by the characters at the beginning of each item, but I can't find a setting or code that will allow me to filter for characters that are contained within the item(s), and not just at the beginning.

Is there a way to do this? Thanks!

 

[am3dz]

is there any way we can make the function dynamic without using listrange?

 

[Akuini]

@am3dz
Welcome to MrExcel

is there any way we can make the function dynamic without using listrange?

Do you mean ListFillRange?
The searchable drop-down I mentioned in post #19 doesn't use ListFillRange & it's dynamic.

 

[XieJingXing]

Is there a way to apply this vba code in all active combo boxes i have??

in my worksheet i have 30 comboboxes. please help to have an auto complete and auto suggest on each combo boxes with the same list fillrange

 

[Akuini]

@XieJingXing, welcome to MrExcel.

Is there a way to apply this vba code in all active combo boxes i have??

Vba code in which post are you referring to?

in my worksheet i have 30 comboboxes.

Do you really that many comboboxes in the sheet?
Why not just use data validation & then use an add-in called “Search deList” to get a searchable combobox.
You can find it here:

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

 

[XieJingXing]

@XieJingXing, welcome to MrExcel.

Vba code in which post are you referring to?

Do you really that many comboboxes in the sheet?
Why not just use data validation & then use an add-in called “Search deList” to get a searchable combobox.
You can find it here:

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

Hello, Sir.

yes. I Have that much comboboxes on my table.
I just want to have an auto suggestion / autocomplete whenever the user enter a data on the active combobox.
As much as I want to have an add-in, my company PC is restricted to have it without their permission.


BTW, thank you for your help.

 

[jasperlaing]

I would just like to thank John_w for the worksheet combobox code. It's an incredibly useful feature and a massive improvement on the regular combobox filtering.

However, I haven't been able eliminate the bug identified in the post above despite spending the weekend on it. I have rearranged the .listrows = and .dropdown lines in every possible combination but the bug persists.

I have attached 5 images in sequence of typing three characters and then backspacing on two of them. The small number next to the combobox is a variable displaying the .listrows value which is correct but the .dropdown appears to be using the .listbox value for the previous entry.

I am having tbh a hard time making sense of it and so I wanted to ask if anyone have a working example of a worksheet combobox without the bug ?

Thanks again