VBA code doesn’t run when cell is changed by a formula

by

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. Create a New Workbook.
  2. 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

enter image description here

Leave a Comment

tech