+ Reply to Thread
Results 1 to 12 of 12

Manual calculation warning?

  1. #1
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Manual calculation warning?

    Is there a way to make excel 2007 pop up a warning whenever calculation is set to manual by a macro or any other means? I have on several occasions noticed formulas not working, only to discover that calculation was set to manual without me noticing. And then I don't know how much of my work may have been afffected. This seems like a pretty vital piece of information, and I am surprised that it's not made more obvious.
    Last edited by jrussell; 09-16-2009 at 09:51 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Manual calculation warning?

    If you're worried about a specific workbook, you could put this code into the workbook VBA
    Please Login or Register  to view this content.
    I tried putting it in my Personal macro book but it gave me an error for some reason. :-/ Maybe saving it as your "Book1" which opens upon opening Excel? Sorry, not my forte.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Manual calculation warning?

    Thanks, but I'm looking for something that will work with all workbooks. Preferably something that is triggered by setting the Application.Calculation = xlManual itself, rather than opening a workbook. I want to know immediately when it happens, so I can correct whatever is causing it.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,480

    Re: Manual calculation warning?

    Changing the Calculation mode does not cause any event to fire.

    So you can monitor for some changes, such as change of active sheet or workbook, but you will not be able to trap the switch when a macro does it.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Manual calculation warning?

    A workaround, then. Would it be possible to check the status of the calculation mode after every round of calculations, or would that bog excel down too much?

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,480

    Re: Manual calculation warning?

    But if the calculation has been set to manual then it will not fire the calculate event so any code in that event will not fire.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Manual calculation warning?

    You could do it in the workbook activate event. It would need to go in the workbook of interest, or could go in a class module in Personal.xls.

    Andy could show us how to do the latter ...
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Manual calculation warning?

    Quote Originally Posted by Andy Pope View Post
    But if the calculation has been set to manual then it will not fire the calculate event so any code in that event will not fire.
    Whoops. But it just illustrates my point: There should be a manual calc warning built in to excel, but there isn't. That's why it always makes me squirrely to write a macro that touches that setting - if it crashes and leaves calc set to manual while being used by someone not particularly skilled in excel, they will be totally stumped as to why their stuff isn't working - if they even notice, which they might not.

    If not a popup, then it should be in the most obvious place possible - there should be an area on the home tab, in front of the clipboard or font area, that explicitly says "Calculation Mode: Automatic | Manual", just like there's an area that displays the font name at all times. I realize there is a place where this is exposed - under the Formulas tab under Calculation - Calculation Options, but this is not obvious.

    Quote Originally Posted by shg View Post
    You could do it in the workbook activate event. It would need to go in the workbook of interest, or could go in a class module in Personal.xls.

    Andy could show us how to do the latter ...
    But anyway, yes, this sounds like what I'm after, or as close as I'm likely to get.

    I just found this, which might be useful to someone reading this: http://www.mrexcel.com/forum/showthread.php?t=49502

    If anyone could help with doing this as a class module, I would very much appreciate it.
    Last edited by jrussell; 09-14-2009 at 01:57 PM.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,480

    Re: Manual calculation warning?

    This is a workbook rather than an addin so you can more easily test it.
    Either change the calculation mode manually and then select another cell or you the test button.

    Class module called CAppEvt

    Please Login or Register  to view this content.
    Standard code module.
    Please Login or Register  to view this content.
    I have added a group to the Home tab that displays a label describing the calculation state.
    It checks every time the cell selection changes.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Manual calculation warning?

    Wow, that works great, Andy! My only question now is, how do I get this to work all the time, with any workbook? Right now it works as long as I'm looking at the workbook you saved. Do I save it as an add-in?

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,480

    Re: Manual calculation warning?

    Save as addin and then open the addin.

  12. #12
    Registered User
    Join Date
    11-11-2008
    Location
    Syracuse NY
    MS-Off Ver
    2007
    Posts
    90

    Re: Manual calculation warning?

    Very awesome! Again, thanks! (Nice postcount, too: 5555.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1