Excel, drop down lists

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.
Asked by: Guest \| Views: 307

Total answers/comments: 1

Comments display order:

Guest [Entry]

"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."