• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation Named Range

S

sabw

New Member
Hi Chandoo,

Thanks for all your help for this novice.

Q. Is it possible to create a drop down box from a named range list that would permit the user to select a value from the first few letters entered and list values would revise as additional letters are entered?

e.g. The named range contains:

Abbotsford

Abbott City

Brahms Point

Bramalea

Caterville

Jacksonville

Johnsonville

Kamloops

In the drop down box the user enters "A" = all entries appear starting with "A".

Enters escalating number of letters until "Abbott" is validated = where Abbott City appears as the only selection.

Many thanks.
 
Hi, sabw!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Data%20Validation%20Named%20Range%20%28for%20sabw%20at%20chandoo.org%29.xlsm


Your data list is in column A, C1 cell has validation list against a named range, E:F columns have a form combo box with link to G1 cell, I:J columns have an ActiveX combo box with link to K1, and there's an user form with a combo box.


With C1 and G1 you can do nothing related to what you want. With K1 and with the user form you can do something alike but not exactly as asked: in both ActiveX combo boxes the property MatchEntry is changed from default frmMatchEntryComplete to frmMatEntryFirstLetter, so you can type a letter the first occurrence of the list beginning with that letter is displayed, and pressing again the same letter will retrieve the next value with such condition, cycling with the first after the end.


What you can't easily do is to press a letter and have displayed or loaded as entries for the drop down (either list or combo boxes of both types). To achieve this you'll have to get involved with macros, VBA code, and dynamically change/load list sources. Which besides of harder might be slow depending on the number of entries in list.


Regards!
 
SirJB7,

Thanks for your kind response. I will work with your example & try to incorporate into my requirements.

Best regards, sabw
 
Hi, sabw!

Glad to help. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top