Activating ManualCalc for any workbook opened
 

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?

Activating ManualCalc for any workbook opened
 

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 wrote:

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

Activating ManualCalc for any workbook opened
 

Thank you very much Dave, it works fine!

It would have taken ages to write this code myself

Thanks again
Ioannis



"Dave Peterson" wrote:

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 wrote:

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