When you go back to the cell later, then press Enter, you trigger the
Worksheet_Change event, and the code runs. There's no way to make this
happen in Excel 97, by selecting an item from the Data Validation list,
if the list source is on a worksheet.

You could select from the list, then press the F2 key, and press Enter.

Or, try a combobox overlay, as described he

http://www.contextures.com/xlDataVal11.html

pyarb wrote:
Debra,

As I mentioned earlier, I've used your code for my workbook and it
kind of works, but with a delayed response. If I click on the drop down
, choose my selection, it does't populate that cell with the
corresponding name unless I pop back into the cell. If I leave the cell
and then go back to the cell than it updates the cell with the
corresponding name. Is there a way to have the event happen on leaving
the cell, once the cell loses focus can the update happen then.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html