Multiple targets with different macro calls in worksheet_change VBA code

Question

I would like to use worksheet_change() to run macro1 if cell1 is changed, macro2 if cell2 is changed, etc. I understand that worksheet_change() only allows target and sh, and that only one sub can be used. I thought I could run something like:

Private Sub Targets(ByVal Target As Range)
Select Case Target.Address
Case "cell1"
Call SheetChange.macro1
Case "cell2"
Call SheetChange.macro2
Case "cell3"
Call SheetChange.macro3
End Select
End Sub

But, apparently I cannot! I also tried

Private Sub Targets(ByVal Target As Range)
If Target.Address="cell1" Then
Call SheetChange.macro1
ElseIf Target.Address="cell2" Then
Call SheetChange.macro2
Elseif Target.Address="cell3" Then
Call SheetChange.macro3
End If
End Sub

But no luck there either. Any help?

Solution 1

See this example. You have to use Intersect to check if a particular cell was changed or not. I am taking the example of A1, A2 and A3

I would also recommend looking at this link which tells you what you need to take care of when working with Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        '~~> Run Macro here
    ElseIf Not Intersect(Target, Range("A2")) Is Nothing Then
        '~~> Run Macro here
    ElseIf Not Intersect(Target, Range("A3")) Is Nothing Then
        '~~> Run Macro here
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

You might also want to handle the situations where user copies and pastes multiple cells. In such a scenario, use this to check it and act appropriately.

    '~~> For Excel 2003
    If Target.Count > 1 Then

    End If

    '~~> For Excel 2007 +        
    If Target.CountLarge > 1 Then

    End If

Solution 2

Here is one way:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    MsgBox Target.Address
    Exit Sub
End If

If Target.Address = "$A$2" Then
    MsgBox Target.Address
    Exit Sub
End If

If Target.Address = "$A$3" Then
    MsgBox Target.Address
    Exit Sub
End If

If Target.Address = "$A$4" Then
    MsgBox Target.Address
    Exit Sub
End If
End Sub

Or if you prefer the select case syntax, you can go this route:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$A$1"
        MsgBox Target.Address
    Case "$A$2"
        MsgBox Target.Address
    Case "$A$3"
        MsgBox Target.Address
    Case "$A$4"
        MsgBox Target.Address
End Select
End Sub

Comments

[kirk]

Can I replace the MsgBox Target.Address with the macro call?

[sous2817]

Yes, just put the message box there as an example. There's nothing stopping you from putting a macro call (or any other valid VBA code) there.

[SeanC]

+1 for the Application.EnableEvents to avoid the stack overflow