Using a Macro to set Calculation Mode to Manual

[mangoel]

Hello All,

I was wondering if it is possible to automatically set the calculation mode in Excel to "Manual" through an workbook_open event or through the press of a command button. The interactions in a workbook that I have created require the calculation mode to be set to manual.

I would really apreciate your help with this. Thanks!

Manish

[s-o-s]

On 2002-05-03 15:34, mangoel wrote:
Hello All,

I was wondering if it is possible to automatically set the calculation mode in Excel to "Manual" through an workbook_open event or through the press of a command button. The interactions in a workbook that I have created require the calculation mode to be set to manual.

I would really apreciate your help with this. Thanks!

Manish

In the This Workbook Object Code paste the following.


Private Sub Workbook_Open()

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

End Sub

Below is some nice code you can add to avoid having to go through the menus all the time.

I have added this to my personal macro book and use ALL the time.

It toggles Calculation on/off and is assigned to a permanent button on my toolbar.

Sub Toggle_Calc()

If Application.Calculation = xlCalculationAutomatic Then

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Else
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End If

End Sub

Hope This Helps


_________________


[ This Message was edited by: s-o-s on 2002-05-03 15:40 ]

[ This Message was edited by: s-o-s on 2002-05-03 15:41 ]

[NateO]

Here's another thought. Get the setting of the end-user when they open the workbook. Then set Excel to manual and when they close the workbook, return Excel the user's original setting. I used auto_macros, these go in a normal module:

Private clcmd As Long

Sub auto_open()
clcmd = Application.Calculation
Application.Calculation = xlManual
End Sub

Sub auto_close()
Application.Calculation = clcmd
End Sub

Make sure your private command is at the top of the module. Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-03 16:01 ]

[mangoel]

Thank you very much for you replies.
I have tried both suggestions and both work for me!

I was just curious, if I choose to use the "Workbook_Open" event to change the calc mode to Manual (in the ThisWorkbook Module), what would the corresponding close workbook event be that I can use to restore the original mode? Is is Workbook_Close?? or something else? Thanks again for all your help!!

Manish

[NateO]

Use the same code (the whole schpeil, variables, vba, etc....) in the "thisworkbook" module, just make substitutions, workbook_open, woorkbook_close.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-04 09:05 ]