当单元格被公式更改时，VBA 代码不会运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11406628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA code doesn't run when cell is changed by a formula
提问by Kenan Fallon
Worksheet Ahas ranges of data that are collected from Worksheet B. Worksheet Ahas a macro that calculates if the data is above a value then calls an email module to email selected users.
工作表 A具有从工作表 B收集的数据范围。工作表 A有一个宏，用于计算数据是否高于某个值，然后调用电子邮件模块向选定的用户发送电子邮件。
When the data is manually input on Worksheet Athe Macro works, however when data is pulled from Worksheet Bit doesn't fire.
当在工作表 A上手动输入数据时，宏工作，但是当从工作表 B 中提取数据时，它不会触发。
I'm not sure what I need to change in my VBA code.
我不确定我需要在 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
回答by Siddharth Rout
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.
要通过公式捕获更改，您必须使用Worksheet_Calculate()事件。为了理解它是如何工作的，让我们举一个例子。
- Create a New Workbook.
- In Sheet1 Cell A1, put this formula
=Sheet2!A1+1
- 创建一个新工作簿。
- 在 Sheet1 Cell 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
And lastly in the ThisWorkbookCode area paste this code
最后在ThisWorkbook代码区域粘贴此代码
Private Sub Workbook_Open()
PrevVal = Sheet1.Range("A1").Value
End Sub
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"
关闭并保存工作簿并重新打开它。现在对 的单元格 A1 进行任何更改Sheet2。你会注意到你会得到消息框MsgBox "Value Changed"
SNAPSHOTS
快照
回答by mkingston
The worksheet_change event will only fire on manual user changes. I think your best bet would be to implement this as a worksheet change event on your Worksheet B, where I presume the user input changes are taking place.
worksheet_change 事件只会在手动用户更改时触发。我认为最好的办法是将其作为工作表 B 上的工作表更改事件来实现，我假设用户输入更改正在发生。
There are some alternatives which I'll suggest if this really doesn't suit you, but I think this is probably by far the best option.
如果这真的不适合您，我会建议一些替代方案，但我认为这可能是迄今为止最好的选择。
Edit: Another suggestion per following comments
编辑：根据以下评论的另一个建议
The ThisWorkbook object has an event SheetChange, which will be fired any time any sheets in the workbook are changed. If you can identify the ranges where data will be entered on each of the B sheets you can then use these ranges as in your original code.
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
Let me know how that goes.
让我知道这是怎么回事。
