VBA code does not run when a cell changes according to the formula

Question

Worksheet A contains data ranges that are collected from Worksheet B. The worksheet has a macro that calculates whether the data exceeds the value above, and then calls the e-mail module to send e-mail to selected users.

When data is entered manually on Worksheet A , the macro works, however, when data is pulled from Worksheet B , it does not work.

I'm not sure what I need to change in 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 

Answer 1

To commit changes to the formula, you must use the Worksheet_Calculate() event. To understand how this works, let's take an example.

• Create a new book.
• In Sheet1 Cell A1 put this formula =Sheet2!A1+1

Now insert this code in the module

 Public PrevVal As Variant 

Paste this into 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 

Finally, in the ThisWorkbook code area, paste this code

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

Close and save the book and open it again. Now make any change to cell A1 Sheet2 . You will notice that you get the MsgBox "Value Changed" message MsgBox "Value Changed"

SNAPSHOTS

Answer 2

The workheet_change event will only fire manually. I believe that it would be best to implement this as a worksheet change event on worksheet B, where I assume that user input changes are occurring.

There are several alternatives that I will offer if this really does not suit you, but I think this is probably the best option.

Edit: another suggestion in the following comments

ThisWorkbook has a SheetChange event that will fire whenever any sheets in the book change. If you can determine the ranges where data will be entered on each of sheets B, you can use these ranges, as in your source code.

 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 this happens.