Worksheet selection change (running marco when cell selected)

[keri]

Hi,

I want to be able to navigate around my sheet by running macro's when
certain cells are selected. I have the following code;


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A49")) Is Nothing Then
showcalendar
End Sub

which works fine, however I have tried numerous ways to have this code
operate based on different cells.
Eg. I also want to run a macro when cell AF95 is selected and a
different macro when cell A53 is selected.

I cannot find a way to use this code more than once. Any help is
appreciated.

Thanks,

 

[NickHK]

Bear in mind that Target can be more than one cell. Depending on what you
want and you determine a hit, a Select Case is useful:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case True
Case Target.Address(False, False) = "A1"
Debug.Print "Call Routine1"
'Call Routine1
Case Not Intersect(Target, Range("F3:M2")) Is Nothing
Debug.Print "Call Routine2"
'Call Routine2
Case Target.Row = 10
Debug.Print "Call Routine3"
'Call Routine3
Case Else
'Do nothing
End Select

End Sub

NickHK

 

[paul.robinson]

Hi
You could try
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target
If .Address = Range("A49").Address Then
showcalendar
ElseIf .Address = Range("AF95").Address
macro1
ElseIf .Address = Range("A53").Address
macro2
End if
End With
End Sub

regards
Paul

 

[Guest]

Keri,

Using the worksheet_Change event you can call as many macro's as you have
patience to extend the If statement:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A49")) Is Nothing Then
MsgBox ("No1 Macro") '<Your nacro call goes here.
ElseIf Not Intersect(Target, Range("A50")) Is Nothing Then
MsgBox ("No2 Macro")
ElseIf Not Intersect(Target, Range("A51")) Is Nothing Then
MsgBox ("No3 Macro")
End If
End Sub

Mike

 

[Guest]

Apologies I missed you were using the selectionchange event so change

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

to

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)