Auto-Enabling/Disabling Auto Calculation

[bonzo]

I have an Excel spreadsheet that has a lot graphs that are build from data that is built with a lot of INDIRECT() calls. As a result, it takes a few minutes for all of the resolution/calculation to complete. Now, since auto-calculation is the default setting, if someone wants to view my spreadsheet (including me), opening the file takes a long time. Since it's my file, I know to turn on manual calculation first and then open the file -- but my colleagues don't always know or remember that.

So, I'm looking for a way (I presume via macros) to turn on manual calculation whenever the file is opened, somehow notify the person opening the file that manual calculation has been turned on, and then do the reverse whenever the file is exited (i.e. turn auto-calculation back on and notify them that this has been done).

Can this be done? If so, any pointers on how? Also, if I have multiple workbooks open, is there a way for auto-calculation to be on for some of the files and manual calculation to be on only for this particular spreadsheet?

Thanks...

 

[bonzo]

On 2002-10-24 17:00, lenze wrote:
What's the point of restoring auto-calculation if no changes were made, as you're going to disable it again when the book is opened?

The calculation stuff apparently controls all of Excel for that session. So, if other workbooks are open when I open this particular one, it gets set to manual. Then when I close it, I still have other workbooks open and manual is still set for all of them too. Now, if I exit all of Excel and then get back in, automatic is reset.

What I'm concerned about is the user who already has Excel open with 1 or more workbooks open and then they try to open my wookbook. I want manual calculation for my workbook and would prefer to maintain automatic calculation (or, really, whatever they have it set to) for their other open workbooks. Does that betterexplain my predicament?

 

[lenze]

See Edit above. I realized the problem after I had posted.

 

[Carp]

The calculation stuff apparently controls all of Excel for that session. So, if other workbooks are open when I open this particular one, it gets set to manual. Then when I close it, I still have other workbooks open and manual is still set for all of them too. Now, if I exit all of Excel and then get back in, automatic is reset.

What I'm concerned about is the user who already has Excel open with 1 or more workbooks open and then they try to open my wookbook. I want manual calculation for my workbook and would prefer to maintain automatic calculation (or, really, whatever they have it set to) for their other open workbooks. Does that betterexplain my predicament?

This is my problem exactly. Does anyone know if there’s a solution?

 

[Carp]

Just one more bump. Sorry.