Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When I created custom drop down lists in excel, my excel sheets don't trim down to a smaller list when I start typing in the drop down list cell.

For example if my drop down list is for USA States [AL...WY] and I type the character 'N' why doesn't my down down list give me a subset of options [...NY...NJ...]. Can subset autocomplete even be done?

Note: I created my excel drop down lists like so:

share|improve this question
What kind of dropdown? – dkusleika Oct 21 '09 at 20:21
up vote 3 down vote accepted

That's because that's not how data validation works in excel. You need to use an active X control. Refer to the Contextures data validation page in general and her ComboBox Data Validation and ComboBox Depend examples in particular.


If you do use her examples, replace the TempCombo_KeyDown event handler with TempCombo_KeyUp. At least on my excel (excel 2003 SP2) it crashes.

More gory details about the example I pointed you to:

I'm not sure why changing TempCombo_KeyDown to TempCombo_KeyUp works, but it may be related to KeyCode. MSDN implies that KeyCode is actually a pointer and is for all practical purposes passed by ref, even though the signature says ByVal. The crash always happens on the End Sub line of TempCombo_KeyDown.

This is just guessing on my part, but by processing the KeyDown event, after the new cell is activated, if it has TempCombo, then the object is activated & can process the KeyUp event. If the target cell doesn't activate TempCombo, the KeyUp event has no valid object reference and you get a nasty crash.

share|improve this answer
Wow MS does make this complex, they should really make this easier in their next version of Excel! – Daniel Oct 26 '09 at 20:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .