How can I run VBA code each time a cell gets its value changed by a formula?

by

If I have a formula in cell A1 (e.g. = B1 * C1) and I want to run some VBA code each time A1 changes due to updates to either cell B1 or C1 then I can use the following:

Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("A1")

    If Not Intersect(target, Range("A1")) Is Nothing Then
    //Run my VBA code
    End If
End Sub

Update

As far as I know the problem with Worksheet_Calculate is that it fires for all cells containing formulae on the spreadsheet and you cannot determine which cell has been re-calculated (i.e. Worksheet_Calculate does not provide a Target object)

To get around this, if you have a bunch of formulas in column A and you want to identify which one has updated and add a comment to that specific cell then I think the following code will achieve that:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim updatedCell As Range
    Set updatedCell = Range(Target.Dependents.Address)

    If Not Intersect(updatedCell, Range("A:A")) Is Nothing Then
       updatedCell.AddComment ("My Comments")
    End If

End Sub

To explain, for a formula to update, one of the input cells into that formula must change e.g. if formula in A1 is =B1 * C1 then either B1 or C1 must change to update A1.

We can use the Worksheet_Change event to detect a cell change on the s/sheet and then use Excel’s auditing functionality to trace the dependents e.g. cell A1 is dependent on both B1 and C1 and, in this instance, the code Target.Dependents.Address would return $A$1 for any change to B1 or C1.

Given this, all we now need to do is to check if the dependent address is in column A (using Intersect). If it is in Column A we can then add comments to the appropriate cell.

Note that this only works for adding comments once only into a cell. If you want to continue to overwrite comments in the same cell you would need to modify the code to check for the existance of comments first and then delete as required.

More Related Contents:

  1. Concatenate column headers if value in rows below is non-blank
  2. Fill formula down till last row in column
  3. Excel VBA – How to add dynamic array formula
  4. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  5. Check if the file exists using VBA
  6. How can I create a progress bar in Excel VBA?
  7. Compare double in VBA precision problem
  8. Row count on the Filtered data
  9. Excel VBA Macro: User Defined Type Not Defined
  10. Automate IE via Excel to fill in a dropdown and continue
  11. Run-Time error 1004 Excel 2013
  12. VBA – Write all possible combinations of 4 columns of data [duplicate]
  13. Pasting an Excel range into an email as a picture
  14. Excel VBA to Export Selected Sheets to PDF
  15. Remove duplicates from array using VBA
  16. Controlling Internet Explorer local intranet using Excel VBA
  17. How to automatically insert a blank row after a group of data
  18. Rounding in MS Access
  19. Count unique values in Excel
  20. Copy sheet and get resulting sheet object?
  21. Sending mail using Outlook where the Send method fails
  22. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  23. Using a variable for row in (row,column) for a range in VBA
  24. Unicode string literals in VBA
  25. How to get the path of current worksheet in VBA?
  26. Excel VBA to match and line up rows
  27. Create a new object using the text name of the class
  28. How to open a workbook specifying its path
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment