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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,487
Messages
5,596,450
Members
414,068
Latest member
FAH

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