0

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.

Improve this question
13
  • 1
    value of the formula what does that mean? Commented Jun 28, 2016 at 17:53
  • 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. Commented Jun 28, 2016 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. Commented Jun 28, 2016 at 18:07
  • Then that's the same thing XD change the value of the cell and not the value of the formula Commented Jun 28, 2016 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 Commented Jun 28, 2016 at 18:13

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Will the worksheet automatically recalculate itself after the user makes the change or do I have to program it?
@petithomme - as long as calculations are set to automatic. If they are manual, this code will fire next time the user calculates.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.