Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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: http://danielk.org/blog/2009/09/22/excel/

share|improve this question
    
What kind of dropdown? –  dkusleika Oct 21 '09 at 20:21

1 Answer 1

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.

Update

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.