Run macros by selecting diff cell in worksheet

[jimmyyy]

Can you add more than 1 Private Sub Worksheet_SelectionChange to a worksheet?

xld, showed me this formula, wich works for runing my (simple macro) the problem im having is how do i add more macros by selecting different cells in the same worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "b15"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Range("B218:219").Select
End With
End If
End Sub

how do i "include in addition to the current code somthing like, if cell b13 is selected 'run macro

Hope this makes sence

[mdmackillop]

Try
[VBA]
Option Explicit
Option Compare Text
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errH
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case "B15"
Range("B218:B219").Select
Case "C15"
Call Macro2(Target)
Case Else
GoTo errH
End Select
errH:
Application.EnableEvents = True
End Sub

Private Sub Macro2(Target As Range)
MsgBox Target.Address
End Sub


[/VBA]

[mdmackillop]

BTW, for something like this, it's OK to ask a follow-up in the original question.
Regards
MD

[jimmyyy]

thanks md i try it out.

[Bob Phillips]

If the actions are completely different, then test each range independently

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE1 As String = "B15"
Const WS_RANGE2 As String = "B13"
If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
With Target
Range("B218:B219").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("X12:Y17").Select
End With
End If
End Sub
[/vba]
However, if the actions are very similar, you cannot take relative action

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B15,B13"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(213, 0).Resize(2, 1).Select
End With
End If
End Sub
[/vba]

[jimmyyy]

[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errH
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case "B15"
Range("B218").Select
Case "B17"
Call Range("B219").Select '<== macro2(target)
Case Else
GoTo errH
End Select
errH:
Application.EnableEvents = True
End Sub
[/VBA]
Tried this but no luck.

Right, i realize i can select multiple cells to activate the same macro.
the actions are completely different, can i do multiple ElsIf Not
im needing so select several different cells to activate several different macros. the macros simply directing me to a diff area on the work sheet.

Il get started on this but it looks almost like its a this or that option, like one of 2 choices.

Any further help feedback is appreciated

Thanks xld, hope i dont were ya out!

[mdmackillop]

You can add as many Case statements as you like. No need to get involved with If's etc.
You only use call if you want to call another macro, so delete it from the Select line.
[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errH
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case "B15"
Range("B218").Select
Case "B17"
Range("B219").Select
Case "B19"
Range("B220").Select
Case "B21"
Range("B225").Select
Case Else
GoTo errH
End Select
errH:
Application.EnableEvents = True
End Sub

[/VBA]

[jimmyyy]

xld, IT WORKED GREAT,

I just added another..

Const WS_RANGE3 As String = "B19"

and another ElsIf not, it looks like this is gona work, Thank You

also thanks md

[jimmyyy]

ill give this a try md, to, iether way i found a solution, that would mak it a lil simpler.