Excel 2007 - Calculation option set to Automatic but Status Bar still says Calculate
Hi Experts,
I'm using Excel 2007 and in an application that uses 3 different XLAM programs, I have a routine in the main Add-in program that executes a macro run from a button on my custom Ribbon. That macro toggles between opening/closing one of the two other Add-ins. These other two Add-ins contain no VBA, only XML code that serves to manipulate Excel's Ribbon. My problem is that when I run the code to toggle these other two XLAM's on/off, the Status Bar then turns on the 'Calculate' message even though the Calculation Options are set to 'Automatic'. Further, pressing F9 does not turn 'Calculate' off.
The code that runs to toggle the two XLAM's on/off follows:
BTW, there are no formulas (or even content) in the worksheets of the two XLAM files, and the main XLAM file contains only about 122 formula cells so this can't be an Excel dependency issue (which is supposed to have been resolved in Excel 2007 according to the page at: http://www.decisionmodels.
Why is this happening and what can I do to fix it? I appreciate any insights.
Jeff
I'm using Excel 2007 and in an application that uses 3 different XLAM programs, I have a routine in the main Add-in program that executes a macro run from a button on my custom Ribbon. That macro toggles between opening/closing one of the two other Add-ins. These other two Add-ins contain no VBA, only XML code that serves to manipulate Excel's Ribbon. My problem is that when I run the code to toggle these other two XLAM's on/off, the Status Bar then turns on the 'Calculate' message even though the Calculation Options are set to 'Automatic'. Further, pressing F9 does not turn 'Calculate' off.
The code that runs to toggle the two XLAM's on/off follows:
Public fullInterface As Boolean
Sub toggleCustomUIOnly(Optional control As Object)
S1 = Application.PathSeparator
T1 = Application.International(xlTimeSeparator)
Dim appPath As String
Dim wBook As Workbook
appPath = GetSetting(myAppName, "InstallPath", "Path")
On Error Resume Next
If fullInterface = True Then
Application.ScreenUpdating = False
SaveCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.StatusBar = "Restoring User Interface. Please wait ..."
Workbooks("Custom Only.xlam").Close savechanges = False
Do While dseKeyPressed(VK_SHIFT)
DoEvents
Loop
Workbooks.Open "Custom and Microsoft Default UI.xlam"
fullInterface = False
Application.Calculation = SaveCalcMode
Application.ScreenUpdating = True
Application.StatusBar = False
Else
Application.ScreenUpdating = False
SaveCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.StatusBar = "Restoring User Interface. Please wait ..."
Workbooks("Custom and Microsoft Default UI.xlam").Close savechanges = False
Do While dseKeyPressed(VK_SHIFT)
DoEvents
Loop
Workbooks.Open "Custom Only.xlam"
fullInterface = True
Application.Calculation = SaveCalcMode
Application.ScreenUpdating = True
Application.StatusBar = False
End If
Err.Clear
On Error GoTo 0
End SubBTW, there are no formulas (or even content) in the worksheets of the two XLAM files, and the main XLAM file contains only about 122 formula cells so this can't be an Excel dependency issue (which is supposed to have been resolved in Excel 2007 according to the page at: http://www.decisionmodels.
Why is this happening and what can I do to fix it? I appreciate any insights.
Jeff
TommySzalapski
The status bar saying 'calculate' means it has made some changes that would require a calculate.
It got lit when calculate was manual but when calculation got set back to automatic, screen updating was off so it didn't unlight.
If you swap them
Application.Calculation = SaveCalcMode
Application.ScreenUpdating
change to
Application.ScreenUpdating
Application.Calculation = SaveCalcMode
It should fix it.
Application.Calculation = SaveCalcMode
Application.ScreenUpdating
change to
Application.ScreenUpdating
Application.Calculation = SaveCalcMode
It should fix it.
ASKER
Hi Tommy and thanks for posting.
I tried your suggestion but it did not clear the 'Calculate' message.
BTW, I have to be away from my computer until about 3 pm EST so I won't be able to respond further until then.
Jeff
I tried your suggestion but it did not clear the 'Calculate' message.
BTW, I have to be away from my computer until about 3 pm EST so I won't be able to respond further until then.
Jeff
Hmm. I can't replicate the error. Comment out the On Error Resume Next line and see if there are errors getting thrown that might be messing things up.
You are checking the options and it is automatic you said? It has to just be stuck on then.
You are checking the options and it is automatic you said? It has to just be stuck on then.
ASKER
As you suggested, I Commented out the On Error Resume Next line, and also turned my VBE Error Handling Options to 'Break on all errors' but the only error I got was on the code like:
Workbooks("Custom and Microsoft Default UI.xlam").Close savechanges = False
... where I was doing a just-in-case closing of an XLAM file, should it be open.
Unfortunately, I still have the same issue with 'Calculate'.
Jeff
Workbooks("Custom and Microsoft Default UI.xlam").Close savechanges = False
... where I was doing a just-in-case closing of an XLAM file, should it be open.
Unfortunately, I still have the same issue with 'Calculate'.
Jeff
I guess you changed the order in both places?
You could try doing this
You could try doing this
Application.ScreenUpdating = True
Calculate
Application.Calculation = SaveCalcMode
Application.StatusBar = FalseASKER
Yes, I changed it in both places ... and tried the latest code above, too. Still no joy.
I will try to put together a test set of files to see if I can reproduce this.
Jeff
I will try to put together a test set of files to see if I can reproduce this.
Jeff
ASKER
Sorry this is taking me so long to keep this question going, but I am having a lot of strange issues in Excel. At any rate, to the case in point, I have uploaded a zipped file that contains a folder ("TestThis") with 3 files:
CallXLAM.xlsm
Custom and Microsoft Default UI.xlam
Custom Only.xlam
TestThis.zip
CallXLAM.xlsm
Custom and Microsoft Default UI.xlam
Custom Only.xlam
TestThis.zip
ASKER
Sorry - Didn't mean to submit that yet. Comments to follow.
ASKER
I have uploaded (in my preceding post at 11/19/10 02:14 PM, ID: 34175677) a zipped file that contains a folder ("TestThis") with 3 files:
CallXLAM.xlsm
Custom and Microsoft Default UI.xlam
Custom Only.xlam
The CallXLAM.xlsm file will toggle between loading one or the other of the XLAM files, whose only function is to load the XML code contained therein to modify the Ribbon or reset it.
After clicking the Button to run the macro, 'Calculate' will be displayed in the Status Bar. I've tried a number of things to get this to work, and the macro code will reflect that ... you'll see a number of Debug.Print statements, too, trying to isolate when/where the CalculationState changes. You should see in the Immediate Window that the CalculationState changes to a "2" which supposedly means:
"xlPending - Changes that trigger calculation have been made, but a recalculation has not yet been performed"
This CalculationState gets changed from '0' to '2' in the preceding "Workbooks.Open" code (that opens the XLAM files) and never gets changed back despite all the subsequent code.
Hopefully, the attached code will be sufficient for others to reproduce this behavior.
Complicating this matter further, when I copy my actual main Add-in file (that houses the code simulated in the CallXLAM.xlsm file) to another PC, the 'Calculate' message does NOT display when the code runs (but the code in the attached TestThis package DOES display 'Calculate' on the alternate PC).
Along the way, I've started encountering messages when saving both the main XLAM program and this example file, that look like this:
Help is non-specific; Answering 'No' and trying to save generates another error like this:
Answering 'Yes' allows the file to save, but I'm still nervous about what is going on here ...
I'm stumped. Hope someone can assist with this.
Thanks,
Jeff
CallXLAM.xlsm
Custom and Microsoft Default UI.xlam
Custom Only.xlam
The CallXLAM.xlsm file will toggle between loading one or the other of the XLAM files, whose only function is to load the XML code contained therein to modify the Ribbon or reset it.
After clicking the Button to run the macro, 'Calculate' will be displayed in the Status Bar. I've tried a number of things to get this to work, and the macro code will reflect that ... you'll see a number of Debug.Print statements, too, trying to isolate when/where the CalculationState changes. You should see in the Immediate Window that the CalculationState changes to a "2" which supposedly means:
"xlPending - Changes that trigger calculation have been made, but a recalculation has not yet been performed"
This CalculationState gets changed from '0' to '2' in the preceding "Workbooks.Open" code (that opens the XLAM files) and never gets changed back despite all the subsequent code.
Hopefully, the attached code will be sufficient for others to reproduce this behavior.
Complicating this matter further, when I copy my actual main Add-in file (that houses the code simulated in the CallXLAM.xlsm file) to another PC, the 'Calculate' message does NOT display when the code runs (but the code in the attached TestThis package DOES display 'Calculate' on the alternate PC).
Along the way, I've started encountering messages when saving both the main XLAM program and this example file, that look like this:
Help is non-specific; Answering 'No' and trying to save generates another error like this:
Answering 'Yes' allows the file to save, but I'm still nervous about what is going on here ...
I'm stumped. Hope someone can assist with this.
Thanks,
Jeff
Okay. I'll need to login to my Excel 2007 computer at the lab. I'll give it a go.
ASKER
Ok, thanks, Tommy. Fingers crossed.
Jeff
Jeff
Okay. If I open your .xlam files, they say Calculate too. So it must be some lingering thing in the custom ribbon or something. Saving and reopening maintains the Calculate stuck on.
ASKER
Got to believe there must be a way to avoid or correct this ... these files have nothing to do with 'Calculation' of anything ... just toggling the hiding/displaying of Microsoft's Default Ribbon Tabs. And again, there's no VBA in those other two files.
Jeff
Jeff
Hmm... This article describes a similar phenomenon, but it doesn't look like it applies here.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243495
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243495
This article is good to but again... maybe not applicable
http://www.decisionmodels.com/calcsecretsf.htm
http://www.decisionmodels.com/calcsecretsf.htm
ASKER
Yeah. The MS link (http://support.microsoft.c
The 2nd link is the same one I mentioned in my original post - I don't see that it applies here either.
Jeff
The 2nd link is the same one I mentioned in my original post - I don't see that it applies here either.
Jeff
ASKER
Well, Tommy, it seems like you and I are hitting a wall here ... with the things we know to try not seeming to provide resolution here. Given that Excel 2007 (& 2010, as well) introduce an entirely different User Interface, not to mention reliance on XML file formats, I've often wished EE had a Zone just for Office 2007+ (or at least Excel 2007+) as it definitely introduces some new wrinkles from time to time (like now), and it's sometimes hard to find Experts with the expertise in this version's idiosyncrasies. I don't know if you know of any other EE Experts with such Excel-2007-specific expertise you might invite to join this topic ... I will contact the moderator to ask that, too.
Thanks for your help, Tommy ... and don't mean to suggest you might not find a solution here but it seems like we are at a dead end at present.
Thanks for your help, Tommy ... and don't mean to suggest you might not find a solution here but it seems like we are at a dead end at present.
That's bizarre if it works, but this is Microsoft and odd things happen often. No problem Jeffery, I know I don't always have all the answers. I ask questions here too.
ASKER
Thanks for the suggestion, ssaqibh. However, I don't believe this is the issue. I tried using both a Forms Control button and an ActiveX Control button, and they both leave the 'Calculate' message on the Status Bar. Further, while the example file I uploaded to EE uses a button to launch the macro, the original problem exists in my XLAM file, where the macro code resides behind a button on my Custom Ribbon, which I think avoids that previous issue with the ActiveX Control button anyway.
Jeff
Jeff
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually, what you would normally do is accept your own post (the last one) as the soluton. This way just in case someone has the same error some time in the future, they can find your post and save themselves hours of stress.
ASKER
Hi Tommy,
I don't mind doing this one way or another but I thought the possibility of someone else running into this bizarre behavior again was pretty slim. But I have no objections to how ever the moderators handle it. Thanks again, Tommy.
Jeff
I don't mind doing this one way or another but I thought the possibility of someone else running into this bizarre behavior again was pretty slim. But I have no objections to how ever the moderators handle it. Thanks again, Tommy.
Jeff
>>I thought the possibility of someone else running into this bizarre behavior again was pretty slim
Yes, but for that one guy...
I've had some pretty hairbrained issues that were resolved by old forum posts.
Yes, but for that one guy...
I've had some pretty hairbrained issues that were resolved by old forum posts.
ASKER
Ok, Tommy, I made the change you suggested.
Jeff
Jeff
ASKER
Found the answer myself (but EE Experts weren't fully informed on the file characteristics).