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

 

[krcranfill]

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

if you enable the developers tab by adding it to your ribbon in the file -> options -> advanced -> customize ribbon menu then you can highlight the line that says "developer" on the left side and then click "add" to grant yourself access to the visual basic toolset.

If you then click on the Macros button and make a new macro, you can paste in the VBA code you want to use. If you want the code above to trigger conditional formatting just pick a cell you don't think anyone will ever use (lets say cell GA1 on worksheet "Sheet2" for example) and add the following line to the end of the if statement in the VBA code just before the part that says "MsgBox "Application.Calculation is set to Manual"":

worksheets("Sheet2").range("GA1").value2 = 1

Next do the same before the msgbox line in the second if statement except change it to:
worksheets("Sheet2").range("GA1").value2 = 0

Now you can make a conditional formatting statement that looks at GA1 and if manual calculation is on the cell will have a value of 1 and if automatic calculation is on it will have a value of 0 (as long as the setting was changed last using your VBA macro)