CODEWITHSUNDEEP
vbaexcel
petithomme
petithomme

Reputation: 549

excel VBA run macro automatically when a cell is changed

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.

Upvotes: 0

Views: 2036

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

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

Upvotes: 2

Related Questions