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?

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

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
>