Automatically run a choice of macros based on cell selection

[emerging_markets]

I need to run a macro based on whether cell P7 in a worksheet contains
value 1, 2 or 3.

If cell contains value 1, would automatically run Macro1, if 2 would
run Macro2, and if 3 then run Macro3.

Can anyone tell me how to do this?

 

[Guest]

Select Case Range("P7")
Case 1
Call macro1
Case 2
Call macro2
Case 3
Call macro3
End Select
End Sub

Regards,
Stefi


„[email protected]†ezt írta:

 

[emerging_markets]

Thanks Stefi

Will this automatically run if the cell value changes? I'm having
difficulty getting it to work. Do I put the code in a normal module or
the sheet code?

Cheers

Mike

 

[Ken Johnson]

Hi Mike,
Try this code in the sheet's code module...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("P7")) Is Nothing Then
Application.EnableEvents = False
Select Case Range("P7")
Case 1
Call macro1
Case 2
Call macro2
Case 3
Call macro3
End Select
End If
Application.EnableEvents = True
End Sub

Ken Johnson

 

[Ken Johnson]

Hi Mike,

Or, you might want to use the Worksheet_Change event instead of the
Worksheet_SelectionChange event. I can't be sure since I don't know how
the value of 1, 2 or 3 finds its way into P7.

Ken Johnson

 

[Dave Peterson]

How does the cell change?

If it's by the user typing, you can tie into the worksheet_change() event.

If it's the result of a formula reevaluating, you could use the
worksheet_calculate event.

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[emerging_markets]

Dear all - many thanks for your help and pointers, I got it to work
fine in the end

Mike