Manual calculation warning?

**jrussell** · 09-14-2009, 10:37 AM

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.

**ChemistB** · 09-14-2009, 11:29 AM

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.

**jrussell** · 09-14-2009, 11:35 AM

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.

**Andy Pope** · 09-14-2009, 11:46 AM

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.

**jrussell** · 09-14-2009, 11:53 AM

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?

**Andy Pope** · 09-14-2009, 11:57 AM

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.

**shg** · 09-14-2009, 11:59 AM

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 ...

**jrussell** · 09-14-2009, 12:33 PM

Originally Posted by Andy Pope

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.

Originally Posted by shg

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.

**Andy Pope** · 09-15-2009, 04:58 AM

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.

**jrussell** · 09-15-2009, 10:15 AM

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?