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.