Type ahead in data validation list

[BrunoBlue]

I have a cell that has data validation, forcing the user to choose from a
drop down list. As it is now, the user must scroll down through the list and
choose the appropriate bit of data, or they can type the full data in as long
as what they type matches the list. What I have been asked to do is make it
so when the user begins typing, the matching data would begin to appear in
full.

Let me explain a little better. The validation list contains the names of
over 50 sites.
what I want to happen is when the characters "AL" are typed the cell begins
to fill in either "ALAN ROAD" or "ALTON DRIVE". The user can then continue to
type or select either site. This is strictly a choose only cell, it does not
modify the list in any way. I hope this makes sense.

 

[Joel]

What you need to to is not to use a validation list. Use a Listbox and fill
like a validation list. then set the property in the listbox "autowordselect
= true."

There are two addition propertiesd you can use in the listbox.

1) ListfillRange - this is the same as the validation list property range
2) Linked Cell - This is a cell that contains the item that is selected in
the listbox.

 

[BrunoBlue]

Ok this works great for the type ahead, thanks, but it raises another issue.
Since it's a box it is not behaving like a cell. In other words I need to be
able to tab away from it, or to it, or press enter to move on. As it is, I
have to click away.
Also the data selected will be used in a formula, how do I reference that?
Any ideas? Thanks in advance.

 

[Joel]

Simply selecting the item in the list box wil put the data into the the
worksheet using "Linked Cell" property of the listbox.

You could put a control button to move the data from the list box to the
cell. both Listbox and control buttons have CLICK events that can trigger a
macro. Yo ucan get even fancier by designing a userform to transfer the
data. Lots of options.

 

[BrunoBlue]

Thanks Joel, I think I have this figured out now.

 

[Lily Ho]

Thanks Joel, I think I have this figured out now.







- Show quoted text -

sorry how does that work....

 

[Gord Dibben]

Lily

Have a look at the combobox method at Debra Dalgleish's site for allowing
autocomplete.

http://www.contextures.on.ca/xlDataVal10.html


Gord Dibben MS Excel MVP