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

More Related Contents:

  1. excel vba compare & copy worksheets from 2 excel files
  2. How to add a named sheet at the end of all Excel sheets?
  3. Copy sheet and get resulting sheet object?
  4. Reducing WithEvent declarations and subs with VBA and ActiveX
  5. How can I loop through a subset of worksheets?
  6. Excel VBA For Each Worksheet Loop
  7. Print function selecting wrong Type of Change option excel vba
  8. Format Cell Contents
  9. Excel formula checklist in employees
  10. How do I put double quotes in a string in vba?
  11. What is the difference between .text, .value, and .value2?
  12. How can I URL encode a string in Excel VBA?
  13. VBA check if file exists
  14. How to insert an embedded picture?
  15. How to call a macro from a button and pass arguments
  16. Obtain attribute names from XML using VBA
  17. Paste Excel range in Outlook
  18. VBa conditional delete loop not working
  19. Run-time error ‘1004’ : Method ‘Range’ of object’_Global’ failed
  20. “Can’t find Project or Library” for standard VBA functions
  21. How do I issue an HTTP GET from Excel VBA for Mac
  22. Update excel sheet based on outlook mail [closed]
  23. Export chart as image – with click of a button
  24. Error13 in Excel VBA in data file
  25. Change font color for a part of text in cell
  26. Create a new object using the text name of the class
  27. Excel cell from which a Function is called [duplicate]
  28. How to extract text within a string of text
  29. GET pictures from a url and then rename the picture
  30. VBA: Modify chart data range

Leave a Comment