Show if Workbook Calculation is Manual

[mikke3141]

I often have a problem that I notice that the automatic calculation is on manual when entering formulas to the cells. Could I have in the Ribbon a traffic lights showing i.e.:

Green = Automatic
Yellow = Automatic except for data tables
Red = Manual

The idea is also that I could press the traffic light to change the setting.
I have been thinking of different VBA solutions, but what would really be the best solution. Thank you for your help.

 

[Von Pookie]

Perhaps create a macro for each calculation setting and put them in a menu?
http://spreadsheetpage.com/index.php/tip/creating_custom_menus/

 

[mikke3141]

It could look something like
Workbook

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

Select Case Application.Calculation
Case xlAutomatic
White
Case Else
Red
End Select

End Sub

Module

Sub Redi()
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Red()
Dim myButton As CommandBarButton
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls(5).Delete
Set myButton = Application.CommandBars("Worksheet Menu Bar").Controls.Add
myButton.Style = msoButtonIcon
myButton.OnAction = "Redi"
myButton.FaceId = 352
End Sub
Sub White()
Dim myButton As CommandBarButton
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls(5).Delete
Set myButton = Application.CommandBars("Worksheet Menu Bar").Controls.Add
myButton.Style = msoButtonIcon
myButton.FaceId = 342
End Sub

However, how can I enter this to the .xla or .xlam file so that it would work always? What would be the best place to put the code? Workbook_SheetChange can slow the normal processes quite much.

 

[mikke3141]

up