VBA code doesn't run when cell is changed by a formula
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
Solution 1:
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 code
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"
SNAPSHOTS