Worksheet_Calculate & Worksheet_Change...Need Something Else

[OldSwimmer1650]

Worksheet_Calculate functions when the worksheet is recalculated.
Worksheet_Change functions when a cell is updated, but not when the cell changes due to a recalculation. For example if cell A4 is the sum of A1:A3 and I change A2 so cell A4 value changes this will not trigger Worksheet_Change.
I'm looking for a function that detects when a cell changes values due to the formula.

 

[OldSwimmer1650]

Worksheet_Calculate functions when the worksheet is recalculated.
Worksheet_Change functions when a cell is updated, but not when the cell changes due to a recalculation. For example if cell A4 is the sum of A1:A3 and I change A2 so cell A4 value changes this will not trigger Worksheet_Change.
I'm looking for a function that detects when a cell changes values due to the formula.

I should have said that Worksheet_Calculate will execute if the cell changes or not. I'm only interested if the cell changes values.

 

[Logit]

Option Explicit

Private Sub Worksheet_Calculate()
Dim ShtChng
Dim Target
On Error Resume Next
'Updateby Extendoffice
    Dim Xrg As Range
    Set Xrg = Range("A1")
    If Not Intersect(Xrg, Range("A1")) Is Nothing Then
Dim KeyCells As Range

Set KeyCells = Range("A1")
On Error Resume Next

Application.ScreenUpdating = False

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    
    'Copy A Range of Data
    Worksheets("Sheet1").Range("A1").Copy

    Worksheets("Sheet2").Activate
    
    'PasteSpecial Values Only
    Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
      
    With Worksheets("Sheet2")
        .Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End With
    
    Worksheets("Sheet2").Range("A1").Select
    
    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Range("H1").Select
 
    Application.EnableEvents = True
  'Else
    'Exit Sub
End If

'Clear Clipboard (removes "marching ants" around your original data set)
Application.CutCopyMode = False
Application.ScreenUpdating = True
 
    End If
End Sub

Paste this in A1 Sheet1 : =H1

Enter any number in H2 and A1 changes. Macro copies A1 and pastes to Sheet 2

 

[mikerickson]

This will trigger if A4 or C4 are changed, even if it is a result of formulas in those cells

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyCells As Range
    
    Set keyCells = Range("A4,C4")
    On Error Resume Next
    Set keyCells = Application.Union(keyCells.Precedents, keyCells)
    On Error GoTo 0

    If Not Application.Intersect(Target, keyCells) Is Nothing Then
        MsgBox Target.Address & " has changed"
    End If
End Sub

 

[Peter_SSs]

For example if cell A4 is the sum of A1:A3 and I change A2 so cell A4 value changes this will not trigger Worksheet_Change.

Actually, it will.
However, you still have the difficulty of detecting if the formula result has actually changed. I think both of the previous suggestions have the same issue in that they will still report a "change" even if the formula result does not actually change.

Assuming that the formula you are monitoring has all its precedent cells on that same worksheet** then I think this will achieve your goal. It requires a helper cell to store the existing formula result. I have used D1 on the same sheet but it could be any cell and that column or row could be hidden or the value could be stored on another worksheet as Logit's code does.

** If my assumption is incorrect, an alternative code can be suggested.

To start, assuming the sheet setup as described in post 1, manually enter the current A4 formula result in D1 then put this Worksheet_Change code in the sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim KeyCell As Range, StoreCell As Range

  Set KeyCell = Range("A4")
  Set StoreCell = Range("D1")
  If KeyCell.Value <> StoreCell.Value Then
    Application.EnableEvents = False
    StoreCell.Value = KeyCell.Value
    Application.EnableEvents = True
    MsgBox KeyCell.Address(0, 0) & " has changed"
  End If
End Sub