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