toggle question

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
Code:
Sub ToggleCalc()
    If Application.Calculation = xlCalculationManual Then
        Application.Calculation = xlCalculationAutomatic
        MsgBox ("Calculation is now set to: AUTOMATIC")
    Else: Application.Calculation = xlCalculationManual
        MsgBox ("Calculation is now set to: MANUAL")
    End If
End Sub

I got sick of going into Tools/Options to change the calculation from Manual to Automatic so I made this quick code to toggle it back and forth. What my question now is: Is there a way I could enhance this to change the words in the menu bar to always show the current state? Instead of using MsgBox, is there a way to alter menu titles to show "Calc: Automatic" or "Calc: Manual"?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this

Code:
Sub ToggleCalc() 
    If Application.Calculation = xlCalculationManual Then 
        Application.Calculation = xlCalculationAutomatic 
        Application.Statusbar = False
    Else: Application.Calculation = xlCalculationManual 
        Application.Statusbar = "Calculation is now set to: MANUAL"
    End If 
End Sub

I think it's only necessary to put the message there when it is set to manual.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

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