Excel, drop down lists

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/

Solution 1:

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.