Calc set to manual with VBA!

[Keibri]

Hi guys,

If it is not too much of an inconvenience to ask, I would really appreciate any help on the below.

I have an excel sheet named 'Planner' which includes a lot of calculations. To cater for this I have included a macro which when the file is opened will switch calculations to manual. Moreover, If someone saves it also disables auto recalculations. Finally when one exists the file automatic calculations will be switched on again. This works fine if I am only using 'Planner'.

The code I inserted is the below:

Option Explicit

'when you open the workbook, switches calculation to manual

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


'when you click save, turn off Calculate Before Save
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     Application.CalculateBeforeSave = False
End Sub

'after the save, switch back to normal
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
     Application.CalculateBeforeSave = True
End Sub

Problem

The only problem I am finding is that if there is another excel file which is already open (which will be on automatic mode), as soon as I open the 'Planner', it freezes everything. I am imagining that this is happening since the other excel file which is already open and is set to automatic calculations is interfering with this code on 'Planner'.

Can the code above be changed to turn all open files to manual calculations as soon as 'Planner' is opened?

Really appreciate a lot your help cause I can't seem to find a solution.

Thanks a lot guys

Keibri