单元格由公式更改时,VBA代码不会运行 [英] VBA code doesn't run when cell is changed by a formula
问题描述
工作表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)
如果Target.Cells.Count> 1然后退出Sub
如果不是Application.Intersect(Range(Address),Target)Is Nothing Then
如果IsNumeric(Target.Value)和Target.Value>值然后
调用Mail_small_Text_Outlook
结束If
Application.EnableEvents = True
End If
End Sub
要通过公式捕获更改,您必须使用 Worksheet_Calculate()
事件。要了解它是如何工作的,我们来举个例子。
- 创建新的工作簿
- Sheet1单元格A1,将此公式
= Sheet2!A1 + 1
现在在模块中粘贴此代码
Public PrevVal As Variant
将其粘贴到工作表代码区域
Private Sub Worksheet_Calculate()
pre>
如果范围(A1)。值<> PrevVal然后
MsgBox更改值
PrevVal =范围(A1)。值
如果
End Sub
最后在
ThisWorkbook
代码区粘贴此代码Private Sub Workbook_Open()
PrevVal = Sheet1.Range(A1)。值
End Sub
关闭并保存工作簿并重新打开。现在对
Sheet2
的单元格A1进行任何更改。您会注意到您将收到消息框MsgBox更改值
SNAPSHOTS
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.
When the data is manually input on Worksheet A the Macro works, however when data is pulled from Worksheet B it doesn't fire.
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
解决方案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.
- Create a New Workbook.
- In Sheet1 Cell A1, put this formula
=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
ThisWorkbook
Code area paste this codePrivate 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 boxMsgBox "Value Changed"
SNAPSHOTS
这篇关于单元格由公式更改时,VBA代码不会运行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!