当单元格被公式更改时，VBA 代码不会运行 [英] VBA code doesn't run when cell is changed by a formula

问题描述

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

Worksheet A has ranges of data that are collected from Worksheet B. Worksheet A has a macro that calculates if the data is above a value then calls an email module to email selected users.

当在工作表 A 上手动输入数据时，宏可以工作，但是当从 工作表 B 中提取数据时，它不会触发.

When the data is manually input on Worksheet A the Macro works, however when data is pulled from Worksheet B it doesn't fire.

我不确定我需要在 VBA 代码中更改什么.

I'm not sure what I need to change in my VBA code.

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

推荐答案

要通过公式捕获更改，您必须使用 Worksheet_Calculate() 事件.为了理解它是如何工作的，让我们举个例子.

To capture the changes by a formula you have to use the Worksheet_Calculate() event. To understand how it works, let's take an example.

1. 创建一个新工作簿.
2. 在Sheet1单元格A1中，输入这个公式=Sheet2!A1+1

现在在模块中粘贴此代码

Now In a module paste this code

Public PrevVal As Variant

将其粘贴到工作表代码区域

Paste this in the Sheet Code area

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

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

And lastly in the ThisWorkbook Code area paste this code

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

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

Close and Save the workbook and reopen it. Now Make any change to the cell A1 of Sheet2. You will notice that you will get the message box MsgBox "Value Changed"

快照

这篇关于当单元格被公式更改时，VBA 代码不会运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！