VBA - Advice on How to turn off or on excel calculation with status in a cell

[VBA learner ITG]

Hi all,

I wonder if I could get your advice if possible to perform the below requirement.

The below code is assigned to a shape within excel which basically turns off or on excel formula calculations when the button is clicked.

Sub btn_autocalc_Click()
If Application.Calculation = xlManual Then
    Application.Calculation = xlAutomatic
    'btn_autocalc.Caption = "TURN FORMULAS OFF MACRO"
Else
   Application.Calculation = xlManual
   'btn_autocalc.Caption = "TURN FORMULAS ON MACRO"
End If
End Sub

But I want to have in a cell below the macro button as a text value to is to show the current status.

For example Text: "Formulas are currently turned off" or "Formulas are currently turned on"

I have tried adding this additional function into my workbook which works by showing if the formulas are on but i cannot get it to show as off if it is off.

Function CalculationMode() As String

    Dim cMode As XlCalculation

    Application.Volatile

    cMode = Application.Calculation

    Select Case cMode

    Case xlCalculationAutomatic: CalculationMode = "Auto"

    Case xlCalculationManual: CalculationMode = "Manual"

    Case xlCalculationSemiautomatic: CalculationMode = "Semi-Auto"

    End Select

End Function

And in a cell =CalculationMode()


Any advice appreicatated.

 

[Joe4]

If you run this little macro, it will show you what values you need to check for the Application.Calculation property:

Sub TestCalcModes()

    Application.Calculation = xlCalculationAutomatic
    MsgBox "Code for Automatic: " & Application.Calculation

    Application.Calculation = xlCalculationManual
    MsgBox "Code for Manual: " & Application.Calculation
    
    Application.Calculation = xlCalculationSemiautomatic
    MsgBox "Code for Semiautomatic: " & Application.Calculation
    
End Sub

 

[VBA learner ITG]

If you run this little macro, it will show you what values you need to check for the Application.Calculation property:

Sub TestCalcModes()

    Application.Calculation = xlCalculationAutomatic
    MsgBox "Code for Automatic: " & Application.Calculation

    Application.Calculation = xlCalculationManual
    MsgBox "Code for Manual: " & Application.Calculation
   
    Application.Calculation = xlCalculationSemiautomatic
    MsgBox "Code for Semiautomatic: " & Application.Calculation
   
End Sub

Thank you for your time and this code.

 

[Joe4]

You are welcome.