Turn AutoCalc before calc takes place

[Mitch Powell]

It is critical that a particular workbook NOT calculate upon opening, so I
used the following:

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub

The problem is that if calculation is is already in auto mode when workbook
is opened, calculation takes place before this code can run and turn it off.
Any ideas?

 

[GTVT06]

It is critical that a particular workbook NOT calculate upon opening, so I
used the following:

Private Sub Workbook_Open()
   Application.Calculation = xlCalculationManual
End Sub

The problem is that if calculation is is already in auto mode when workbook
is opened, calculation takes place before this code can run and turn it off.  
Any ideas?

What if you were to have the workbook set the calculation mode to
manual before closing the workbook so next time it's opened it's
already set to manual and wont auto calculate?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationManual
End Sub

 

[Mitch Powell]

Won't work since I can't control the mode that Excel is in when the workbook
is opened. Most users' machines are in autocalc mode as a matter of course.

 

[Gord Dibben]

GTVT06

Note: if a workbook set to automatic calculation is opened prior to opening the
workbook with the BeforeClose code, calculation will revert to automatic because
the first workbook sets the mode for subsequent workbooks.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

How about creating a 2nd workbook to open the real workbook.

It can change the calculation mode to manual, then open the real workbook, then
close itself.

 

[Mitch Powell]

Thought of that would prefer not to if there is another solution (just
another step in the user experience). Looks like there isn't another
solution so this is probably the way to go. Thanks.