Solved

Excel 2007 - Calculation option set to Automatic but Status Bar still says Calculate

Posted on 2010-11-18
31
812 Views
Last Modified: 2012-05-10
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:

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 Sub

Open in new window


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.com/calcsecretsf.htm.  I don't believe any of the other reasons for the 'Calculate' message discussed at this page apply either.

Why is this happening and what can I do to fix it? I appreciate any insights.

Jeff
0
Comment
Question by:jeffreywsmith
  • 15
  • 12
31 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34165757
The status bar saying 'calculate' means it has made some changes that would require a calculate.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34165771
It got lit when calculate was manual but when calculation got set back to automatic, screen updating was off so it didn't unlight.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34165784
If you swap them

    Application.Calculation = SaveCalcMode
    Application.ScreenUpdating = True


change to


    Application.ScreenUpdating = True
    Application.Calculation = SaveCalcMode

It should fix it.
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34165990
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

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34166260
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.
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34168757
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

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34168887
I guess you changed the order in both places?
You could try doing this
    Application.ScreenUpdating = True
    Calculate
    Application.Calculation = SaveCalcMode
    Application.StatusBar = False

Open in new window

Again, I can't make it happen on mine so it's hard to test.
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34172246
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
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34175677
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
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34175689
Sorry - Didn't mean to submit that yet. Comments to follow.
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34175994
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:

 screen shot
Help is non-specific; Answering 'No' and trying to save generates another error like this:

 2nd screen shot
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
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34176297
Okay. I'll need to login to my Excel 2007 computer at the lab. I'll give it a go.
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34176519
Ok, thanks, Tommy. Fingers crossed.

Jeff
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34177269
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34177325
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
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34177571
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
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34177582
This article is good to but again... maybe not applicable
http://www.decisionmodels.com/calcsecretsf.htm
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34177677
Yeah. The MS link (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243495) is for XL2003 & prior (The 65K dependency limit has purportedly been removed in XL2007 and shouldn't have been invoked by the ~ 130 calculated cells in my file.

The 2nd link is the same one I mentioned in my original post - I don't see that it applies here either.

Jeff
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34180145
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.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 34181514
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34181556
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.
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34183442
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
0
 
LVL 2

Accepted Solution

by:
jeffreywsmith earned 0 total points
ID: 34200606
Well, I guess I need to apologize for leading everyone on a wild goose chase here.  What I have found out is that I apparently had inadvertently 'locked' the two minor XLAM files using a pretty exotic Excel protection package (LockXLS) for my application, and the "locking" mechanism they use was causing this "Calculate" message somehow.  At any rate, I didn't need these two files locked anyway, so I reverted back to an earlier version of the two XLAM files and all is well again.  

Sorry, I didn't realize that I had done this ... and, of course, no one at EE could have figured this out without knowing about it, so my bad.

I expect the thing to do here is to ask that this question be deleted but I do appreciate everyone's efforts.

Jeff
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34200665
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.
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34200717
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
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34200755
>>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.
0
 
LVL 2

Author Comment

by:jeffreywsmith
ID: 34200870
Ok, Tommy, I made the change you suggested.

Jeff
0
 
LVL 2

Author Closing Comment

by:jeffreywsmith
ID: 34224676
Found the answer myself (but EE Experts weren't fully informed on the file characteristics).
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now