在 worksheet_change VBA 代码中具有不同宏调用的多个目标

Multiple targets with different macro calls in worksheet_change VBA code

提问by kirk

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:

我想使用 worksheet_change() 在 cell1 更改时运行 macro1，如果 cell2 更改则使用 macro2，等等。我知道 worksheet_change() 只允许 target 和 sh，并且只能使用一个 sub 。我以为我可以运行类似的东西：

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?

但也没有运气。有什么帮助吗？

回答by Siddharth Rout

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

请参阅此示例。您必须使用Intersect来检查特定单元格是否已更改。我以A1,A2和A3

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

我还建议您查看此链接，该链接告诉您在使用时需要注意什么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

回答by sous2817

Here is one way:

这是一种方法：

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

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

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

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

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

或者，如果你更喜欢 select case 语法，你可以走这条路：

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