Cell driven enable auto calculation for worksheet

[RPM7]

I'm trying to create a macro that will enable and disable auto calculation on specific sheets in my workbook. I have over 40 sheets in the workbook and it's very slow due to all the calculations taking place. Is there a way to make the calculations manual on certain worksheets?

My first worksheet is like a dashboard/directory, that I want to use to control all the other sheets.
I have all the sheets labelled in one column and adjacent to it is another column that says Yes/No.
Each cell in the second column is named (Calc_Tab_1 to Calc_Tab_40)

So if Calc_Tab_3 = "No", then the calculations on sheet 3 should be manual.

I've added this code to a module.

Sub Calc()
If [Calc_Tab10] = "Yes" Then
Call TurnOnCalc_Tab10
Else
Call TurnOffCalc_Tab10
End If

End Sub

I've added this code into the specific sheet.

Sub TurnOffCalc_Tab10()
  Application.Calculation = xlCalculationManual
End Sub

Sub TurnOnCalc_Tab10()
  Application.Calculation = xlCalculationAutomatic
End Sub

Is there a way to make this work, or is there a better way to control the calculations on each worksheet?

Thanks

 

[rondeondo]

hi.
I'm pretty sure autocalculate is a whole workbook level functionality. So if it is off when you make changes on sheet 1 then go to sheet 2 and turn it on it will the recalculate the first sheet.
A trick that may help, turn off autocalculate, to recalculate a sheet or part of a sheet select the portion of sheet to recalculate and do a search and replace for the = symbol. So find = and replace it with =.
When you do this the formulas that are selected will recalculate but leave the rest.
You can do it without selecting first and it will do the whole sheet.
You could build a macro to do this and put a button on a ribbon or assign it to a shortcut key.