Solved - Formula Calculation Mode - Inadvertently Changing To Manual

Originally Posted by MrShorty

I'm not exactly sure how it would work. Calculation mode is a "global" setting, so what I suspect is happening is something like

1) You open a workbook that was saved with calculation mode to manual. Excel picks up on that and sets calculation mode to manual
2) While this book is open, you open one of yours. Since calculation mode is a global setting, Excel has to decide whether to set calc mode to automatic or to leave it as manual and change the setting on the newly opened book.
3) If it decides to leave calc mode to manual, and you save your book, now your book has the manual tag added to it.

This appears to be exactly what is happening. I made a bunch of test spreadsheets tonight, one at a time. I set some to manual and left others on automatic. I then tested out different combinations of which ones I opened first, etc.

If I open one up that is set to manual, then while keeping that first spreadsheet open, I open another than was previously set to automatic, Excel will change the second spreadsheet to manual calculation mode. The second spreadsheet will only retain that manual calculation mode if I save it while the global settings caused it to be in manual mode. If I close down the second spreadsheet without saving it, it will not retain the temporary manual mode setting it showed while it was open.

The same is true in reverse, if I open a spreadsheet that is automatic mode first, then open up a second spreadsheet that previously was set to manual mode, it will temporarily change the second spreadsheet to automatic mode. Again, that second spreadsheet will only retain that automatic setting if I save it while it is in that temporarly mode caused by the global settings.

With as many spreadsheets and I have to open and close all day long it is no wonder this 'global settings' feature has caused me so much grief. Depending on what I have open or closed this manual calculations mode is spreading like a virus and that is exactly how I got infected in the first place is opening up a spreadsheet someone else sent me that was set to manual, as I have never had a need to set any of my spreadsheets to manual myself.

Thanks for helping me to figure this out.