PDA

View Full Version : stop formulae from updating



wakwak1
01-13-2008, 05:20 PM
I have a sheet that has dynamic formulae which keep updating certain cells. I want to have a button that "freezes" the current screen and "locks in" all the values (no there is no further updating).

a simple eg. : Cell J14 is 10, K14 is 5, L14 = J14 / K14 (which is 2). Now I want to "freeze" cell K14 so that if i change J14 or K14, L14 is still 2. Then, it would be nice if there was an "unfreeze" type function. Thanks !

mikerickson
01-13-2008, 06:21 PM
Sub toggleCalculation()
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
Else
Application.Calculation = xlManual
End If
End Sub

wakwak1
01-13-2008, 08:43 PM
thanks!

any idea about the code to change the label of the button?

mikerickson
01-14-2008, 12:28 AM
If the button is from the Forms Menu, this works for me.

Sub toggleCalculation()
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Caption = "make manual"

Else
Application.Calculation = xlManual
ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Caption = "make automatic"
End If
End Sub

wakwak1
01-14-2008, 01:11 PM
hmmm, but i get a "type mismatch" error for the lines:

ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Caption = "make manual"

and

ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Caption = "make automatic"

My button is just from the "Control Toolbox" part of the VBA toolbar in Excel. Thoughts??

Bob Phillips
01-14-2008, 01:44 PM
Private Sub CommandButton1_Click()
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
Me.OLEObjects("CommandButton1").Object.Caption = "make manual"

Else
Application.Calculation = xlManual
Me.OLEObjects("CommandButton1").Object.Caption = "make automatic"
End If
End Sub