Manual Calculate for Excel Workbook

[Edmund]

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

 

[Edmund]

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