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?
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
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