excel VBA run macro automatically when a cell is changed

Question

I found a lot of answers to my question following this link: automatically execute an Excel macro on a cell change

The reason I'm opening a new question is because I don't have enough reputation to comment on that thread and I'm having difficulty connecting my linked cell to the macro I want to run.

So the cells that are linked contains a formula causing it to change value only when I change other cells. The problem I'm having is that my macro only runs if I change the value of the cell and not the value of the formula. I'm looking for a way to activate the macro when the value that the formula returns changes.

This is my code so far:

My range of cells is named "Values" and I want to hide the label "Refresh"

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Me.Range("Values")) Is Nothing Then Exit Sub
        Application.EnableEvents = False 'to prevent endless loop

        Sheet1.Refresh.Visible = False

        Application.EnableEvents = True
End Sub

Sorry again for opening another question but as I said I couldn't comment on the other thread.

You're going to need to archive the values in the cells and then check to see if they are the same whenever you change something else. Alternatively, you could do the intersection on the cells that CAUSE the formulas to evaluate to something else. Then, whenever one of these "cause" cells changes, you can hide the label. — OpiesDad
@findwindow Sorry, I meant the value that the cell gets by calculating the formula so if it was 2 + 2 = 4 it would be 4. — petithomme
Then that's the same thing XD change the value of the cell and not the value of the formula — findwindow
@Opies I thought someone would suggest that. In my worksheet, the user uses drop lists to choose his answers. These anwers are linked to values in another sheet. So when the user changes the answer and therefore the value that the formula uses, no cell value actually changed, it just gets the value of another cell. I can give more information if that's not enough — petithomme

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-06-28T18:33:13

Use this code. It stores the values into a cell on the worksheet that is the last row\column. You'll need to store the value there manually once, but after that it will store it for you each time there's a change so it can check the next time the sheet is calculated (and the formula result is potentially changed).

Private Sub Worksheet_Calculate()

Dim bOld as Byte
bOld = Me.Cells(Me.Rows.Count,Me.Columns.Count)

If Me.Range("Values") <> bOld Then 

    Application.EnableEvents = False 'to prevent endless loop
    Me.Cells(Me.Rows.Count,Me.Columns.Count).Value = Me.Range("Values").Value 'store new value
    Sheet1.Refresh.Visible = False
    Application.EnableEvents = True

End If

End Sub

excel VBA run macro automatically when a cell is changed

1 Answers