stop formulae from updating

[wakwak1]

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]

[VBA]Sub toggleCalculation()
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
Else
Application.Calculation = xlManual
End If
End Sub[/VBA]

[wakwak1]

thanks!

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

[mikerickson]

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]

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]

[vba]

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
[/vba]