Automatic Calculation

[APML]

Hi All, I have a workbook with lots of worksheets that perform many calculations. To make the workbook perform better I've set up the below macro which turns on and off automatic calculations for some of the sheets.
I'm just wondering if anyone can suggest a way, so I can know with a quick glance if automatic or manual calculation is set for the specifc workheet..
I was thinking something like a cell might change colour or something... was looking for a way that doesn't require me to do a calculation to find out if the sheet is set to automatic or manual


Sub TurnOff()

Worksheets("ASX_Data").EnableCalculation = False

Worksheets("Analysis").EnableCalculation = False

Worksheets("TodaysData").EnableCalculation = False

MsgBox ("AutoCalc Is OFF")

End Sub

Sub TurnOn()

Worksheets("ASX_Data").EnableCalculation = True

Worksheets("Analysis").EnableCalculation = True

Worksheets("TodaysData").EnableCalculation = True
MsgBox ("AutoCalc Is ON")
End Sub

 

[Anthony47]

Maybe the colour of the worksheet tab?

Worksheets("ASX_Data").EnableCalculation = False
Worksheets("ASX_Data").Tab.Color = RGB(255, 0, 0)    'Red=Disabled

Worksheets("ASX_Data").EnableCalculation = True
Worksheets("ASX_Data").Tab.Color = RGB(0, 255, 0)   'Green=enabled

If you already use colors and prefer not modifying them, then you might use TintAndShade:

Worksheets("ASX_Data").EnableCalculation = False
Worksheets("ASX_Data").Tab.TintAndShade = -1    '-1 set the tab to black

Worksheets("ASX_Data").EnableCalculation = True
Worksheets("ASX_Data").Tab.TintAndShade = 0     '0 restores the original colour

 

[APML]

Hey thats sounds fantastic..I'm really happy to have the tabs colour change. I'm not very good at vba.. Do I just insert the line into my code, or is it a separate module..And do I need to do it for each worksheet, at the moment I'm switching 3 worksheets on and off automatic calculation at the same time

 

[Anthony47]

Just add the line that manages the tab colour to your code; ie

Sub TurnOff()

Worksheets("ASX_Data").EnableCalculation = False
Worksheets("ASX_Data").Tab.Color = RGB(255, 0, 0)

Worksheets("Analysis").EnableCalculation = False
Worksheets("Analysis").Tab.Color = RGB(255, 0, 0)

Worksheets("TodaysData").EnableCalculation = False
Worksheets("TodayData").Tab.Color = RGB(255, 0, 0)

MsgBox ("AutoCalc Is OFF")

End Sub

You don't need to modify the colours for the other tabs, other then for cosmetics choices

 

[APML]

So I added the did the follow but got an error:

Sub TurnOff()
Worksheets("ASX_Data").EnableCalculation = False
Worksheets("ASX_Data").Tab.Color = RGB(255, 0, 0)
Worksheets("Analysis").EnableCalculation = False
Worksheets("TodaysData").EnableCalculation = False
Worksheets("ASX_Companies").EnableCalculation = False

MsgBox ("AutoCalc Is OFF")
End Sub

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

MsgBox ("AutoCalc Is ON")



End Sub

 

[Anthony47]

Which line? Wich error?

Is it clear that you set Red only the ASX_Data tab, whereas you disable calculation on 4 sheets?
And, since you don't rework the color when re-enable claculation, the tab will stay red forever

 

[APML]

So I've modified the code and taken a screen shot of the problem, btw i really appreciate the help.

 

[Anthony47]

Do you get an error message?
Is the code inserted in a Standard Vba Module of the workbook that contains those sheets?
Are you on a Window environment?

 

[APML]

Yes, pls see error screen shot. And yes so far as i know it's a standard vba module (i'm not so good at vba so not entirely sure).
Yes use windows 10

 

[Anthony47]

Is the worksheet ASX_Data Visible or Hidden? Is the worksheet or the workbook protected?
If you grab the yellow arrow (near the yellow vba line) and move it on the next line (so skipping color setting the ASX_Data tab) and press F5 (to continue the macro) what happens?
What happens if you run Sub TurnOn?