Auto Complete a select from a data validation list

[Lele]

I am using a list of 5,000 items stored on a separate worksheet in the same
work book for data validation of the items being selected on my order form.
The data validation feature seems to be working ok, when you select the
down-arrow, the list appears. However, with 5,000 potential selections,
scrolling to your choice is too time consuming. Can I make this list work
more like a combo box, so the user can time the first few letters, and the
list will search for matching items?

 

[Debra Dalgleish]

If you can use programming, there's a sample file here that provides a
combobox from which you can select one of the values from the data
validation list. In the combobox, you can enable autocomplete:

http://www.contextures.com/xlDataVal11.html

But with a list that long, it may help users to select from a category
first, then select an item from that category, using dependent lists:

http://www.contextures.com/xlDataVal02.html

 

[Lele]

Thanks so much Debra! It worked great, and I am not even a programmer,
although I can copy paste with the best of them.

I do have another question however. I want to be able to "lock down" the
worksheet, so the user does not accidentally type over some of the functions
on other parts of the worksheet. Unfortunately, when I apply protection the
autofill combo box feature no longer works. Is there a solution?

Thanks again.

Lele

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.

If the worksheet is being protected, allow users to Edit Objects, and
they should be able to use the combobox.

 

[Lele]

Wow! It is so easy when you have the answer!!!
Thanks again.
You're the best.

 

[aschulze]

Debra,

Your website is great and very helpful! I am trying to create a combo box
with autocomplete for a workbook that I am creating. When I copy and paste
the code into my workbook, I am assuming I need to change something in the
code to reference my combo box. Could you tell me what needs changed? I
can't seem to figure it out. The name of my combo box is StoreListCombo.
I would appreciate any help.

 

[Gord Dibben]

Debra's code refers to a combobox named "TempCombo"

If your is named "StoreListCombo" edit the sheet code to that name.

Change both instances of Set cboTemp = ws.OLEObjects("TempCombo")


Gord Dibben MS Excel MVP