Macro launching when a cell value changes due to a formula not by the user

Question

I would like my Macro to launch whenever a value in a cell containing a formula changes. i.e. the user is modifying another cell thus changing the value of the cell in question.

I have noticed that using the statement (found herein), only works if the user modifies the cell itself but not if the cell changes automatically - due to a formula as specified above.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A20")) Is Nothing Then ...

Any thoughts??

I tried to follow the answers from this question "automatically execute an Excel macro on a cell change" but it did not work...

Thanks in advance :)

Answer 1

A possible work-around comes from the fact that, to change a value, the user needs to change the selection first. So I would:

1) Declare a global variable called "oldValue" on top of the WS source code module:

Dim oldValue As Variant

2) Register the old value of your formula before the user types anything (let's say it's in Range("A4"), I let you adapt with the others):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    oldValue = Range("A4")
End Sub

3) Check if the change has affected the formula in the Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A4") <> oldValue Then
        MsgBox "User action has affected your formula"
    End If
End Sub

I've tested with a simple sum, I'm able to write cells that are not involved without any prompt but if I touch one of the cells involved in the sum the MsgBox will show up. I let you adapt for multiple cases, for user adding/removing rows (in that case I suggest to name the ranges containing the formulas you want to track) and the worksheet references.

EDIT I'd like to do it at once, not by going through 2 processes, is it possible? The problem is my macro involves a range containing more than one cell so it will be hard to store old values for 10 cells.

If ranges are next to each other, then instead of using a variable you can use a collection:

Dim oldValues As New Collection
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    For j = 1 To 10
        oldValues.Add Range("A" & j).Value
    Next j
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    For j = 1 To 10
        If Range("A" & j).Value <> oldValues(j) Then
            MsgBox "The value of Range(A" & j & ") has changed"
        End If
    Next j
End Sub

Of course, if ranges are not close to each other, you can just store them anyway in the SelectionChange event like this:

oldValues.Add Range("A1").Value
oldValues.Add Range("B7").Value
'...

and if you done this ONCE, with 10 ranges only, it should be a reasonable solution to your problem.

Answer 2

You said, "I would like my Macro to launch whenever a value in a cell containing a formula changes..."

If having your code run whenever a cell containing a formula is recalculated (which is not exactly what you asked for), one solution might be to create a VBA function that simply returns that value passed to it, plus does whatever else you want to do when the formula is recalculated...

Public Function Hook(ByVal vValue As Variant) As Variant
    Hook = vValue

    ' Add your code here...
End Function

...then "wrap" your formula in a call to this function. For example, if the formula you are interested in is =A1+1, you would change this to =Hook(A1+1), and the Hook function would be called whenever A1+1 is recalculated (for example, when the value in A1 changes). However, it is possible that recalculating A1+1 will yield the same result and still call the Hook function (for example, if the user re-enters the same value in A1).

Answer 3

Declaring a module -level variable like Matteo describes is definitely one good way to go.

Brian 's answer is on the right track with regards to keeping all is the code in the same place, but it's missing one critical part : Application.Caller

When used in function that is called by a single cell, Application.Caller will return the Range object of that cell. This way you can store the old value within the function itself when it is called, then once you're done with calculating the new value you can compare it with the old and run more code as required.

Edit: The advantage with Application.Caller is that the solution scales in and of itself, and does not change no matter how the target cells are arranged (I.e. Continuous or not).

Answer 4

You can have a go at this:

First, in a Module Code declare a Public Variable.

Public r As Range, myVal '<~~ Place it in Module

Second, initialize your variables in Workbook_Open event.

Private Sub Workbook_Open()
    Set r = Sheet1.Range("C2:C3") '<~~ Change to your actual sheet and range
    myVal = Application.Transpose(r)
End Sub

Finally, set up your Worksheet_Calculate event.

Private Sub Worksheet_Calculate()
    On Error GoTo halt
    With Application
        .EnableEvents = False
        If Join(myVal) <> Join(.Transpose(r)) Then
            MsgBox "Something changed in your range"
            '~~> You put your cool stuff here
        End If
        myVal = .Transpose(r)
forward:
        .EnableEvents = True
    End With
    Exit Sub
halt:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume forward
End Sub

Above will trigger the event when values in C2:C3 changes.
Not really very neat but works in detecting changes in your target range. HTH.