Excel 2003 calculations

January 26, 2010 at 09:59:43
Specs: Windows XP
Why does calculation in Excel 2003 change from automatic to manual

See More: Excel 2003 calculations

Report •


#1
January 26, 2010 at 10:07:09
One reason is that Excel will follow the "instructions" of the first workbook that is opened in any given Excel session.

If the first workbook that is opened is set to Manual, all subsequent workbooks will be set to Manual for as long as Excel is open.

Another way is an Open Event macro in a workbook that tells Excel to set the calculation to manual.

There could be other ways, but I'd need more info before I could offer anything else.


Report •

#2
January 26, 2010 at 10:26:20
Thank you for the follow-up
Read the "Calculation Settings" string
and believe this may be the resolution as tried everything else mentioned in this string
Name: antoonvdr
Date: May 1, 2009 at 17:22:16 Pacific
+2
Reply:
I have finally found it!
It was a bug in ASAP Utilities that happened in the version up to 4.5.1. It is now fixed, see http://www.asap-utilities.com/faq-q...


Report •

#3
January 26, 2010 at 10:46:53
Well, not the problem as end-user doesn't have ASAP Utilities

Apparently just out of the blue the first workbook opened in Excel session this morning, which was also used day before, decides to change its calculation settings from automatic to manual under the options/tool/calculation tab.

So still a mystery why the selection would change without the end-user adjusting it. Read that there may be hidden personal.xls causing this... how would I find this document?


Report •

Related Solutions

#4
January 26, 2010 at 11:01:37
Open any workbook and click on the Window menu button.

If Unhide is greyed out, you don't have a hidden personal.xls or any hidden file open.

If Unhide is dark, scroll down to Unhide and a dialog box will open showing the names of any hidden windows.

Also make sure you look at the bottom of the Window menu to see if there are any other windows (unhidden) that might be open.


Report •

#5
January 26, 2010 at 11:14:02
Well, unhide is grey and nope, there are no other excel windows open. So guess this isn't the problem. Have asked IT if installed any addons that would cause this problem and they are not aware of any.

Any other ideas that may be causing the automatic calculate to change to manual through no user action?


Report •

#6
January 26, 2010 at 11:18:26
re: "Any other ideas...? "

Nope! Sorry.


Report •

#7
January 26, 2010 at 11:30:08
Okay. Thanks for your time. If anyone else comes up with an idea would love to hear it!

How about a macro to run each time a workbook is opened to ensure automatic calculation is running? If this is a viable idea, how would I write this macro?


Report •

#8
January 26, 2010 at 11:41:12
Only other thing may be if you use a template, the mode of calculation is the mode that is specified in the template....

MIKE

http://www.skeptic.com/


Report •

#9
January 26, 2010 at 12:04:33
I hate to even suggest this, because you shouldn't have to do this. You should find (or at least eliminate) the root cause of the problem, even if that takes a re-install of Office.

To force calculation to Automatic, we have to go back to my earlier statement: Excel will follow the "instructions" of the first workbook that is opened in any given Excel session.

Therefore, you would need to open a workbook that is set to Automatic before any other workbooks are open, including a new workbook. The only way I know how to do this is to create a personal.xls workbook, store it in the XLSTART folder so that Excel can find it and then place the code below in the ThisWorkbook module of the personal.xls workbook.

Since Excel will look in the XLSTART folder and open personal.xls first if it finds one, the code will run as soon as Excel opens the file, forcing personal.xls to be Automatic and any subsequent workbooks that open should follow along.

Of course, I have to add this disclaimer: If whatever is going on changes the setting after the workbooks are opened, then this solution isn't going to work. The code will force personal.xls to Automatic everytime, but it's not going to prevent anything that happens after the file is open.

Note: You could put this code in the ThisWorkbook module of every existing workbook to deal with each workbook individually, but once again, you shouldn't have to do this at all. And, once again, if the setting is being changed sometime after the workbook is opened, forcing Automatic on Open is only going to help up until time the setting gets changed.

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


Report •

#10
January 26, 2010 at 12:54:47
I really appreciate your follow-up with a potentional solution. And certainly understand your disclaimer. We'll give it a try and watch to see if happening after the workbook(s) have been open for awhile.

Report •


Ask Question