OnAction & Drop Down List

[MariaG]

I have a drop down list in a cell on the worksheet. Once
User makes a selection from the drop down list, I would
like Excel to run a Macro based on the Item Selected or at
least to know that the User has made a change to the drop
down list.

Can anyone help me with this code?

Thanks in advance.
MariaG

 

[Tom Ogilvy]

If the dropdown you speak of is produced by data validation, you the
Worksheet_change event in versions later than xl97.

Otherwise, you if from the forms toolbar, then assign a macro to it, or for
a combobox from the control toolbox toolbar, use the click event.

 

[MariaG]

Tom,

Thank you for your response. My Drop Down is produced by
Data Validation. I have tried your suggestion with:

Private Sub Worksheet_Change(ByVal Target as Range)
Target.Font.ColorIndex = 5
End Sub

It works when I >>type<< in a change, but not when I
select a different item from the Drop Down List.

Any help would be appreciated. Thx

MariaG

 

[MariaG]

Tom,
I did not read your post correctly. I am using Excel 97.
Sorry for the inconvenience. I suppose there is no way in
Excel 97?
Thx.
MariaG

 

[Tom Ogilvy]

If Excel 97, then change is not fired. If excel 2000 or later, then this
should work.

In Excel 97, you would need to reference this cell with a formula, then use
the calculate event. So the action would happen each time a calculate was
performed - a lot of extra cycles, but that is pretty much it. (in xl97,
it will work if you type in the dropdown choices directly in the textbox of
the validation dialog, but not if you use a range).