Freezing calculation

[Dave Walker]

I have a worksheet where some cells use the values of other cells to
calculate their value. What I would like to be able to do is turn this
calculation on and off based on another cell value. I'd like to be able to
have the cell either calculate a new value based on the other cells or freeze
the calculation and keep its old value even when the linked cell values
change. Ideally, the freeze/unfreeze would be controlled by another cell
value. This is basically a reversible and automatic version of what you can
do by pasting the value of a formula into a cell.

Is there any way to do this?

Thanks,
Dave

 

[Luke M]

You can *sorta* do that, but it'll take a bit of work. Since you can't set
calculation settings by cell, you have to speicfiy which cells you want to
calculate at certain times. First, Under tools-options-calculation, set
calculation to automatic.

Now, right click on the sheet tab of concern, view code, paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
'Duplicate/modify following line as needed
If Range("A1") = 5 Then Range("B1").Calculate
End Sub


'Modify this macro, adjusting ranges and values to fit your desire.
Depending on the extent of how many cells you have, this should be able to
give the desired outcome.

 

[Dave Walker]

I tried this and found a couple of things. First, I had to set calculation
to "manual" in the options tab. Everything worked as advertised within
single sheets. Where I had a problem was with links between sheets. For
example, if Sheet1!A1 depended on Sheet2!A1 then Sheet1 did not update when
the value on Sheet2 changed. ANy ideas?

Dave

 

[Luke M]

My apologies on the automatic/manual confusion. A case where brain thought
one thing, fingers typed another. For intersheet relations:

Placing the code on the Sheet2 that has precedent value, you can modify
coding to:

Private Sub Worksheet_Change(ByVal Target As Range)
'Duplicate/modify following line as needed
If Range("A1") = 5 Then Sheets("Sheet1").Range("A1").Calculate
End Sub


'So, you would place this within the sheet2 module. Then, when Sheet2!A1 =
5, the macro will calculate A1 on Sheet1.

 

[Dave Walker]

It works! Thanks for the help.

Dave

My apologies on the automatic/manual confusion. A case where brain thought
one thing, fingers typed another. For intersheet relations:

Placing the code on the Sheet2 that has precedent value, you can modify
coding to:

Private Sub Worksheet_Change(ByVal Target As Range)
'Duplicate/modify following line as needed
If Range("A1") = 5 Then Sheets("Sheet1").Range("A1").Calculate
End Sub


'So, you would place this within the sheet2 module. Then, when Sheet2!A1 =
5, the macro will calculate A1 on Sheet1.



--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*