Calc set to manual with VBA!

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:

Please Login or Register  to view this content.

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

Why bother with this at all?

If you need to have calcs set to manual (because of lots of/slow calcs?), why not just set the file to manual calc. It will open and calc, only calc when you tell it, and then calc on save

Hi FDibbins,

In effect you are right. However what was happening is that some users were switching the other files to automatic mode and then when this file is being opened it is not turning 'Planner' to manual cause I belief the calculation mode of one open document changes the mode for all open documents

Hi,

You can't do that with code in a normal workbook because it will not run the code until the workbook has been calculated if another workbook is open already in automatic calculation mode. You could create an additional workbook that sets calculation to manual and then opens the actual planner, and have the users open that workbook instead. In my opinion you would be better off trying to improve the calculation of your workbook instead of inflicting manual calculation on all your users which will almost inevitably cause problems.