How to stop automatic calculation

[Guest]

it is default set to manual, and calculation is done by my macro. everything
is fine. However, my workbook will be mess up and start calculate everything
when user forget to close my workbook and open another workbook. I guess it
is because the other workbooks default is automatic and it change my working
environment background to automatic. How can I prevent my workbook calculate
itself when another wookbook is open?

 

[Dave Peterson]

Excel changes the calculation mode based on the first workbook you open in that
session.

If your users open your workbook first (with calculation set to manual), then it
should be manual.

But if they open another workbook first (with calc set to automatic), then when
they open your workbook, calculation will still be automatic.

One way around this is to give the users an intermediate workbook that sets the
calculation to manual, then opens your workbook.

This is the kind of code in the helper workbook:

Option explicit
Sub Auto_open()
Application.Calculation = xlCalculationManual
workbooks.open(filename:="C:\myworkbook.xls")
thisworkbook.close savechanges:=true
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm