Dropdown box suggested list option by typing the first letter of an option

I don't think I am explaining my issue correctly in the title, or I think it will be confusing if I did.

I have a dropdown box (data validation) with many options to select from. What I want to know is that if it's possible to have the dropdown box give me suggested options after typing the first letter/first few letters. Pretty much like any search out there like google.

Here's an example of what I mean just to clarify:

My list in my drop down consists of: "Apple", "Apricot", "Banana", "Blueberry", "Kiwi", and "Plum"

Now in the drop down, I type "A" it suggests that I want to select "Apple" or if I keep typing and type "Apr" it suggests I want to select "Apricot"

Another example: In the drop down I type "P" it suggests that I want to select "Plum"

Not sure if this is actually possible, but I figured i'd ask. I hope this explanation has been clear enough for others to understand.

This works, only trouble is you need to Click "Enter" after you enter you first few letters in the validation cell , for the code to work.
So if you type in "Pl" in "C1" then click "Enter" you Get "Plum"
Alter validation cell (C1 in this Case) to suit.
NB:- Copy code into sheet module.

Please Login or Register  to view this content.

Regards Mick

Hey Mick, thanks for the response. Unfortunately, this is not working. I have changed C1 in the code to where my drop down is located (B10). I try typing the first two letters and press enter it gives me the data validation error where the value doesn't match the data validation restrictions defined for the cell. I press cancel, and then VBA wants to pop up and then excel crashes. Not sure if you can help, but this is whats happening.

To make this easier, my workbook is attached. The only focus is the drop down boxes B10-B450 in sheet "W2 Friday". Everything else is irrelevant.

Try this returned file:-
NB:- The code now works quite well, but for some strange reason if you type "Wo" in any of the validation cells you get "Works Object" which is in "B9" but not in the validation lists. I don't know why !!!

How about the attached.

Thank you so much! That works fantastic. Is there anything else you did besides add the code to VBA? I just finished my project without the "searchable" drop downs. I opened it back up and added the code to each sheet (this now has many sheets) and it doesn't work with this new file. I even tried it with one sheet and it still doesn't work on my final file.

If it's something simple then please let me know. If not, then don't worry about it. You've done enough, I'll just redo it all with the file you've sent me. Attached is the finale file if you do want to work with it.

Now everything in ThisWorkbook code module.

You guys are the best! Thank you so much.

I apologize for opening this back up. I cannot figure out why the code trips an error when protecting the worksheets. It works fine until I protect it and what's highlighted in the debugger doesn't make much sense to me. What am I missing?

Attached is the same spreadsheet with minor fixes and for obvious reasons, it's attached unprotected.

To be clear, I want only the yellow fields on each sheet to be unlocked and the rest of it locked.

Sheets are not protected...

Can you upload the workbook with your setting?