多个目标与工作表中的不同的宏调用VBA代码 [英] Multiple targets with different macro calls in worksheet_change VBA code

问题描述

如果cell1被更改，我想使用worksheet_change（）运行macro1，如果cell2被改变，则使用macro2等等。我理解，worksheet_change（）只允许target和sh，并且只能使用一个子。我以为我可以运行如下：

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?

推荐答案

看到这个例子。您必须使用相交来检查特定单元格是否更改。我正在以 A1 ， A2 和 A3

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

我还建议您查看链接，告诉您使用 Worksheet_Change

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

这篇关于多个目标与工作表中的不同的宏调用VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！