Activating ManualCalc for any workbook opened

[johnmasvou]

I want to get excel to turn to manual calculation for any workbook used.
I thought I could use an add-in to create an automatic procedure with the
workbook open event, in order to make it generic:

Sub Workbook_Open()
If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
End Sub

However, this seems to work only on individual workbooks since the add-in
doesn't perform any action when I open a random workbook.

Any ideas?

 

[Dave Peterson]

The workbook_Open event will fire only when the workbook with the code is
opened. So you could add that same kind of code to every workbook that needs it
-- or you could use something called an application event.

These application events run when something happens at the application
level--not specific to a worksheet or workbook.

You may want to try this (and all the code goes in the ThisWorkbook module):

Option Explicit
Private WithEvents XLApp As Excel.Application
Private Sub Workbook_Open()
Set XLApp = Excel.Application
End Sub
Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
ChangeTheCalculationMode
End Sub
Private Sub XLApp_WorkbookOpen(ByVal Wb As Workbook)
ChangeTheCalculationMode
End Sub
Sub ChangeTheCalculationMode()
Dim TempWkbk As Workbook
If ActiveWorkbook Is Nothing Then
Application.EnableEvents = False
Set TempWkbk = Workbooks.Add(1)
Application.EnableEvents = True
End If
XLApp.Calculation = xlCalculationManual
XLApp.Iteration = True
XLApp.MaxIterations = 9999
If TempWkbk Is Nothing Then
'do nothing
Else
TempWkbk.Close savechanges:=False
End If
End Sub

 

[johnmasvou]

Thank you very much Dave, it works fine!

It would have taken ages to write this code myself

Thanks again
Ioannis