Code to switch Calculation Mode ManualAuto (VBA Ex'97)

[mangoel]

Hello All,

I am trying to use VBA routines in a workbook to manually switch the Calculation mode back and forth from Manual/Automatic.
By default, when the workbook opens, I would like the mode to be set to manual, and then on I would like to use subroutine calls to change the mode. eg. I might need to change the calculation mode to manual from automatic when a certain worksheet is activated. What is the best way to do this? I would like this code to work with Excel 97.

I tried the following routines, but am getting errors changing the modes. I would really appreciate any help on this. Thanks!

Manish

Public Sub SwitchToManualCalc()
With Application
If .Calculation = xlAutomatic Then
.Calculation = xlManual
.MaxChange = 0.001
End If
End With
End Sub

Public Sub SwitchToAutomaticCalc()
With Application
If .Calculation = xlManual Then
.Calculation = xlAutomatic
.MaxChange = 0.001
End If
End With
End Sub

[Nimrod]

I have a few sheets that I do this with as well and just leave the thing on manual and use the Calculate now feature.
I've never tested to see if the option is manual or automatic ... I just calculate.

Sub CalcSheet()
 Application.MaxChange = 0.001
    ActiveWorkbook.PrecisionAsDisplayed = False
    ActiveSheet.Calculate
End Sub

[Ivan F Moala]

What Error are you getting and how are you calling the routine form an ActiveX commandbutton , msoshape or some other means.
If the former then try setting your cmdbtn
property [TakeFocus*******] to False.