Show if Workbook Calculation is Manual

mikke3141

Board Regular
Joined
Nov 11, 2008
Messages
88
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mikke3141

Board Regular
Joined
Nov 11, 2008
Messages
88
It could look something like
Workbook
Code:
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
Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,190
Messages
5,835,896
Members
430,393
Latest member
hetherton

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top