Run VBA macro when cell is updated - not by user or another macro

Question

I have a spreadsheet, which uses an external add-on that updates values in a cell. Notice, the cell is not changed, only the value is updated.

I would like to run a macro every time the cell is updated.

To simulate the problem:

Create an empty excel sheet
In cell A1 add the formula '=rand()'
In VBA add a macro for sheet1, here a simplified version:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Cells(1, 3).Value = "The function updated" End If

End Sub

However, when I thereafter press F9 (to update the random numbers), the sheet does not recognize this as a sheet change, hence the macro is not run. How can I make the macro run when e.g. a random number in the sheet i updated?

You'll need a combination of the Application.Calculate event to detect when the value might have changed and some functionality to check if the actual value of the cell(s) have changed since the event was last triggered. — CLR
I think the OP means the cell in question contains a formula that refers to other cells that are changing - but is simulating this with the rand function in their example. — CLR
@CLR Yes, indeed, I read the question again. I will delete my comment.. — FaneDuru
@CLR Would please give an example? I tried using the Calculate event, but it ended closing excel every time I ran an update (F9). — RVA92
@Joe-Hague has given you exactly what I was describing below. — CLR

Joe Hague Joe Hague · Accepted Answer · 2021-06-29T12:51:12

The way I would tackle this is to set a global variable pointing to the cell in question (as double) as follows:

    Global clvalue As Double

Then have a workbook open event which sets the variable to the cell value as follows:

    Private Sub Workbook_Open()
        clvalue = Round(Sheet1.Range("C5").Value, 2)
    End Sub

The a worksheet Calculate event to do the comparison and resetting of the global variable to the new cell value as follows:

    Private Sub Worksheet_Calculate()

        If Sheet1.Range("C5").Value <> clvalue Then
            Debug.Print clvalue
            clvalue = Sheet1.Range("C5").Value
            Debug.Print "New Global Variable is now " & clvalue
       End If
    End Sub

Run VBA macro when cell is updated - not by user or another macro

1 Answers