Excel 2007 Manual Calculation Switch Problem

[Salut39]

Hi all,
I am using this code to switch to Manual Calculation and back when Open workbook.

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

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

The problem is that it doesn't work on more complex spreadsheets when other books are open with Auto calculation on. It starts recalculating until I press ESC and then it swithes to Manual. Is there the way to fix it?

Yuri
Basic Knowledge VBA

 

[davedave24]

Perhaps try Workbook_Activate or Workbook_Initialize

 

[Salut39]

Thank you for the hint which doesn't help me much as my skills go as far as copying code into modules. Should I replace Workbook_Open()
with Workbook_Activate?

 

[combo]

You should at least undersand what the code does, where it should be placed and when it fires. It's worth to consider the sequence of switching calculations on/off and its current state.

The procedure Workbook_Open() is an event procedure and fires automatically when located in ThisWorkbook object module. It behaves as a regular procedure in other modules. It fires when a workbook with this code is beeing open (assuming all security conditions are satisfied). The same refers to Workbook_BeforeClose(Cancel As Boolean).
The Application.Calculation property is global, so it refers to all workbooks in a given excel instance. When you close a workbook with Workbook_BeforeClose(Cancel As Boolean) procedure, the calculation is switched to automatic, even if previously opened workbooks set it to manual.
You may consider Application.CalculateBeforeSave property too, when set and calculation is set to manual, workbooks are recalculated before save.

combo

 

[Salut39]

I understand what it does, but I simply need a code which stops recalculating particular workbook regardless if that's possible?

 

[combo]

Have you checked that the recalculation takes place when recalculation is set to manual? It's an whole excel instance settings, one can't switch it off for a specific workbook (in opposite, one can force recalculation selectively).

combo