VBA to Turn on or off auto calculate

[APML]

Hi All, I'm not so good at VBA. I have a sheet with multiple tabs and lots of calculations. I've found it necessary to switch off and on auto-calculation on some tabs so the sheet can perform better. The 1st code below works fine but I'm hoping to use the same macro to turn off and on auto calculate on another TAB at the same time. My modified code isn't doing it. Would appreciate all advice. FYI the worksheets are called ASX_Data and Analysis

SO THIS WORKS WITH 1 SHEEET IN THE WORKBOOK
Sub TurnOff()
Worksheets("ASX_Data").EnableCalculation = False

MsgBox ("AutoCalc Is OFF")
End Sub

Sub TurnOn()
Worksheets("ASX_Data").EnableCalculation = True

MsgBox ("AutoCalc Is ON")

End Sub




THIS DOESN'T WORK

Sub TurnOff()
Worksheets("ASX_Data, Analysis").EnableCalculation = False

MsgBox ("AutoCalc Is OFF")
End Sub

Sub TurnOn()
Worksheets("ASX_Data,Analysis").EnableCalculation = True

MsgBox ("AutoCalc Is ON")

End Sub

 

[johnnyL]

Separate lines of code for each sheet
Example:

Sub TurnOff()
    Worksheets("ASX_Data").EnableCalculation = False
    Worksheets("Analysis").EnableCalculation = False
'
    MsgBox ("AutoCalc Is OFF")
End Sub

 

[APML]

Thx, works well. Really appreciate the help

 

[johnnyL]

Glad to help.

 

[APML]

Thx, works well. Really appreciate the help

Glad to help.

just wonder, can you suggest a way that i'd quickly know that the auto-calculate is of (or on) on the sheet.
was thinking along the lines that I could conditional formal a cell to change color (or something) if the auto cal was off, not really sure..
but would be handy if I had a quick way to glance at the sheet and know the setting ...

 

[johnnyL]

To check via vba code:

Sub CheckAutocalculationMode()
'
    Application.Calculation = xlCalculationManual   ' (-4135)
'
    If Application.Calculation = -4135 Then
        MsgBox "Application.Calculation is set to Manual"
    End If
'

'
    Application.Calculation = xlCalculationAutomatic ' (-4105)
    If Application.Calculation = -4105 Then
        MsgBox "Application.Calculation is set to Automatic"
    End If
End Sub

See if that helps you.

 

[APML]

To check via vba code:

Sub CheckAutocalculationMode()
'
    Application.Calculation = xlCalculationManual   ' (-4135)
'
    If Application.Calculation = -4135 Then
        MsgBox "Application.Calculation is set to Manual"
    End If
'

'
    Application.Calculation = xlCalculationAutomatic ' (-4105)
    If Application.Calculation = -4105 Then
        MsgBox "Application.Calculation is set to Automatic"
    End If
End Sub

See if that helps you.

Thx.. i'm not so good at vba, so not sure what to do with this?... can this some how trigger a conditional format and change color a a cell