当单元格被公式改变时，VBA代码无法运行

Question

工作表 A 包含从 工作表 B 收集的数据范围。 工作表 A 中有一个宏计算数据是否超过某个值，然后调用电子邮件模块向选定的用户发送电子邮件。

当手动在 工作表 A 上输入数据时，宏可以正常工作，但是当从 工作表 B 中提取数据时，它无法触发。

我不确定需要在 VBA 代码中做出哪些更改。

Private Sub Worksheet_Change(ByVal Target As Range)
    Call MailAlert(Target, "B5:M5", 4) 
    Call MailAlert(Target, "B8:M8", 7) 
    Call MailAlert(Target, "B11:M11", 6)
    Call MailAlert(Target, "B14:M14", 2) 
    Call MailAlert(Target, "B17:M17", 4) 
    Call MailAlert(Target, "B20:M20", 1) 
    Call MailAlert(Target, "B23:M23", 3) 
    Call MailAlert(Target, "B26:M26", 1) 
    Call MailAlert(Target, "B29:M29", 5) 
    Call MailAlert(Target, "B32:M32", 1) 
    Call MailAlert(Target, "B35:M35", 7) 
    Call MailAlert(Target, "B38:M38", 20) 
    Call MailAlert(Target, "B41:M41", 0) 
End Sub

Private Sub MailAlert(ByVal Target As Range, ByVal Address As String, ByVal Value As Integer)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range(Address), Target) Is Nothing Then
        If IsNumeric(Target.Value) And Target.Value > Value Then
        Call Mail_small_Text_Outlook
        End If
        Application.EnableEvents = True
    End If
End Sub

- Kenan Fallon

Answer 1

要通过公式捕获更改，您必须使用Worksheet_Calculate（）事件。为了理解它的工作原理，让我们举一个例子。

1. 创建一个新工作簿。
2. 在Sheet1单元格A1中，放置此公式=Sheet2！A1 + 1

现在，在模块中粘贴此代码

Public PrevVal As Variant

将此粘贴到“表格代码区域”

Private Sub Worksheet_Calculate()
    If Range("A1").Value <> PrevVal Then
        MsgBox "Value Changed"
        PrevVal = Range("A1").Value
    End If
End Sub

最后在 ThisWorkbook 代码区粘贴此代码

Private Sub Workbook_Open()
    PrevVal = Sheet1.Range("A1").Value
End Sub

关闭并保存工作簿，然后重新打开它。现在对Sheet2的单元格A1进行任何更改。您会注意到会收到消息框MsgBox "Value Changed"

快照

- Siddharth Rout

---

我该如何在一系列单元格上使用它？ - Moritz Schmitz v. Hülst

Answer 2

worksheet_change事件只会在手动用户更改时触发。我认为你最好将其实现为Worksheet B上的工作表更改事件，因为我假设用户输入更改是在那里进行的。

如果这真的不适合你，还有一些替代方案，但我认为这可能是迄今为止最好的选择。

编辑：根据以下评论的建议

ThisWorkbook对象具有SheetChange事件，该事件将在工作簿中任何工作表更改时触发。如果您可以确定每个B工作表上将输入数据的范围，则可以像原始代码中一样使用这些范围。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Sh Is Sheets("Worksheet A") Then
        If Intersect(Sh.Range("B1:B5"), Target) Then
            'Call MailAlert as required here
        ElseIf Intersect(Sh.Range("B10:B20"), Target) Then
            'Call MailAlert as required here
        Else ' Etc...
            'Call MailAlert as required here
        End If
    End If
End Sub

让我知道进展如何。

- mkingston

---

有12个“工作表B”（每个月一个），所以我会对您的其他建议感兴趣。澄清一下，工作表A将所有月份的工作表编制成易于阅读的格式。 - Kenan Fallon

那么，工作表A中的公式大概是这样的=WorksheetJan!A1+WorksheetFeb!A2+WorksheetMar!A3...等等？我马上要下班了，大约1小时后再回答。 - mkingston

每个工作表B都有一组值，而工作表A只需使用简单的 ='WorksheetB'!B31 等语句来提取它们。 - Kenan Fallon

看起来不错，但是当指定范围内的值超过目标值时，我该如何调用MailAlert模块呢？不过我要等到明天才能测试。 - Kenan Fallon

快了，它没有调用MailAlert子程序。（请参见原始代码发布） - Kenan Fallon