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.

If you're worried about a specific workbook, you could put this code into the workbook VBA

Private Sub Workbook_Open()

   If Application.Calculation = xlManual Then
   MsgBox ("You are set to manual Calculations")
   Else
   MsgBox ("You are not set to manual calculations")
End If
End Sub

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.

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.

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.

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

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.

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

Option Explicit

Private WithEvents m_appXL As Application
Private m_lngCalculation As XlCalculation
Private m_rbxRibbonUI As IRibbonUI
Public Property Set RibbonUI(RHS As IRibbonUI)
    Set m_rbxRibbonUI = RHS
End Property


Private Sub Class_Initialize()
    Set m_appXL = Application
End Sub

Private Sub Class_Terminate()
    Set m_appXL = Nothing
End Sub


Private Sub m_appXL_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Application.Calculation <> m_lngCalculation Then
        m_lngCalculation = Application.Calculation
        m_rbxRibbonUI.InvalidateControl "rbxCalcMode"
    End If
    
End Sub

Standard code module.

Private m_clsAppEvt As CAppEvt

Public Sub rbxCalcMode_getLabel(control As IRibbonControl, ByRef returnedVal)
'
' Code for getLabel callback. Ribbon control labelControl
'
    Select Case Application.Calculation
    Case xlCalculationManual
        returnedVal = "Manual"
    Case xlCalculationSemiautomatic
        returnedVal = "Semi"
    Case Else
        returnedVal = "Automatic"
    End Select
    
End Sub
Public Sub rbxRibbon_onLoad(ribbon As IRibbonUI)
'
' Code for onLoad callback. Ribbon control customUI
'
    Set m_clsAppEvt = New CAppEvt
    Set m_clsAppEvt.RibbonUI = ribbon
    
End Sub
Sub TestMe()

    Select Case Int(Rnd() * 6) Mod 3
    Case 0
        Application.Calculation = xlCalculationAutomatic
        Range("A1") = "Automatic"
    Case 1
        Application.Calculation = xlCalculationManual
        Range("A1") = "Manual"
    Case 2
        Application.Calculation = xlCalculationSemiautomatic
        Range("A1") = "Semi"
    End Select
    
End Sub

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.

Save as addin and then open the addin.