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, Jun 28 '16 at 17:55
@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, Jun 28 '16 at 18:07
Then that's the same thing XD `change the value of the cell and not the value of the formula` — findwindow, Jun 28 '16 at 18:10
@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, Jun 28 '16 at 18:13
@findwindow The thing that activates the macro is when the formula is changed. I want my macro to run when the outcome of the formula changes. (without the formula itself changing) — petithomme, Jun 28 '16 at 18:15
are you looking for just *any* change in values or are there specific values that the formula will produce (reason being that if there specific outcomes (or a lack of specific outcomes, it may be easy to code with `Worksheet_Calculate` event). — Scott Holtzman, Jun 28 '16 at 18:16
@Scott Yeah actually the values in those cells can only be 0, 1, 2, 3, 4 — petithomme, Jun 28 '16 at 18:19
@Scott But if it's easier to look out for _any_ change I can do it that way too — petithomme, Jun 28 '16 at 18:22
okay - is it any change ... from 0 to 1 or 2 to 1 or 4 to 2 that will cause the trigger? Or do you want the trigger to occur when the values are certain numbers? — Scott Holtzman, Jun 28 '16 at 18:28
@Scott Basically, if the value changes, I want the macro to run. No matter the value it was or is now. — petithomme, Jun 28 '16 at 18:29
0 to 1, 1 to 2, 2 to 4, 4 to 0, 3 to 2. It will all cause the macro to run.* — petithomme, Jun 28 '16 at 18:33

score 2 · Accepted Answer · answered Jun 28 '16 at 18:33

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

Will the worksheet automatically recalculate itself after the user makes the change or do I have to program it? — petithomme, Jun 28 '16 at 18:37
@petithomme - as long as calculations are set to automatic. If they are manual, this code will fire next time the user calculates. — Scott Holtzman, Jun 28 '16 at 18:42

excel VBA run macro automatically when a cell is changed

1 Answers1

Linked