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

 

[APML]

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?

Yes sheets are protected, would that be causing the problem

 

[Alex Blakenburg]

The only way that I can generate that error is for the Workbook protection to be turned on.
Do you have Workbook protection on ?

If you are unsure right click on the Tab and if is looks like this it is turned on.

 

[Anthony47]

Workbook protection was also for me the only way to get the error...

Could you try the Sub TurnOff and Sub TurnOn on a new workbook?

 

[APML]

The only way that I can generate that error is for the Workbook protection to be turned on.
Do you have Workbook protection on ?

If you are unsure right click on the Tab and if is looks like this it is turned on.

View attachment 64191

You're a genius, yes the workbook was locked. I've unlocked and problem solved. I locked the workbook so i couldn't accidentally change the tab order, which I sometimes do... but i can live with that. Thanks for your help, i really appreciate it

 

[Alex Blakenburg]

@APML, @Anthony47 provided most of the solution, I was late to the party

 

[APML]

Workbook protection was also for me the only way to get the error...

Could you try the Sub TurnOff and Sub TurnOn on a new workbook?

Thanks for your help with this

 

[APML]

Workbook protection was also for me the only way to get the error...

Could you try the Sub TurnOff and Sub TurnOn on a new workbook?

Hey really appreciate the help, thx

 

[Alex Blakenburg]

Can I suggest that what solved your issue of flagging a worksheets as having Calc turned on or off , was either Anthony's post #2 or his post #4 and that it would be appropriate to mark one of those as a solution ?

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...

 

[Anthony47]

Can I suggest that what solved your issue of flagging a worksheets as having Calc turned on or off , was either Anthony's post #2 or his post #4 [...]

Thank you Alex, anyway I don't bother getting the badge; indeed I am here to learn "on the head of other users"

 

[Alex Blakenburg]

Thank you Alex, anyway I don't bother getting the badge; indeed I am here to learn "on the head of other users"

I think that is probably true for most of us. Love your work by the way.