Worksheet selection change (running marco when cell selected)
 

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,

Worksheet selection change (running marco when cell selected)
 

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

"keri" wrote in message
ups.com...
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,

Worksheet selection change (running marco when cell selected)
 

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

On Jun 8, 10:22 am, keri wrote:
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,

Worksheet selection change (running marco when cell selected)
 

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

"keri" wrote:

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,

Worksheet selection change (running marco when cell selected)
 

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)

"Mike H" wrote:

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

"keri" wrote:

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,