Hi All,

I have Excel 2003 and am trying to get a workbook not to auto-calculate.

The workbook is opened through a button on the menu bar. In the procedure I
set Application.Calculation = xlCalculationManual, then open the workbook.
In the workbook's Deactivate event I set Application.Calculation =
xlCalculationAutomatic and in the Activate event I set it back to
xlCalculationManual. However, when the I set the Calculation in the
Deactivate event the workbook I don't want to be calculated is calculated.

Next I tried to use the workbook's Protect method with an empty password.
Again, the workbook calculates in the Deactivate event.

Anyone have any ideas on how a workbook can be set not to auto-calculate
while leaving the others on auto-calculate?

Thanks

Ed

I've figured it out, without having to use the workbook events.

Public Sub OpenNoCalculate()
Dim wk As Workbook, ws As Worksheet
Dim calcSetting As XlCalculation
Dim calcB4SaveSetting As Boolean

calcSetting = Application.Calculation
calcB4SaveSetting = Application.CalculateBeforeSave

Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = False

Set wb = Workbooks.Open(Filename:="file")

For Each ws In wb.Worksheets
ws.EnableCalculation = False
Next ws

Application.Calculation = calcSetting
Application.CalculateBeforeSave = calcB4SaveSetting

End Sub

Thanks anyway,

Edmund

"Edmund" wrote in message
...
Hi All,

I have Excel 2003 and am trying to get a workbook not to auto-calculate.

The workbook is opened through a button on the menu bar. In the procedure
I set Application.Calculation = xlCalculationManual, then open the
workbook. In the workbook's Deactivate event I set Application.Calculation
= xlCalculationAutomatic and in the Activate event I set it back to
xlCalculationManual. However, when the I set the Calculation in the
Deactivate event the workbook I don't want to be calculated is calculated.

Next I tried to use the workbook's Protect method with an empty password.
Again, the workbook calculates in the Deactivate event.

Anyone have any ideas on how a workbook can be set not to auto-calculate
while leaving the others on auto-calculate?

Thanks

Ed