TurnOff AutoCalc on one sheet only

[davew]

Hi

Is is possible to prevent auto calculation on one sheet only and leave the rest auto calcing?

I've scoured google and tried several suggestions but each one disables the entire workbook not just the sheet. The latest code I've used is below and it's pasted into the worksheet code.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
End Sub


I'd like to disable one sheet rather than disable the whole workbook and make only some sheets auto calc.

Any help would be much appreciated.

Kind regards

Davew

[Kenneth Hobs]

Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
End Sub

Private Sub Worksheet_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub

[davew]

Hi Kenneth

Thanks for your reply however I can't get this code to work.

I've pasted the first 3 lines into the worksheet code of sheet 1 and the next 3 into that of sheet 2. Initially calc's are set to the default automatic. On visiting sheet 1, calc's are indeed manual but this then makes the rest of the sheets manual. Weirdly the automatic element has also stopped me from being able to copy and paste from sheet 2 into any other sheets.

I've attached a workbook to show you what I mean.

Kind regards

Davew

[Kenneth Hobs]

All the code goes into the sheet that you want manual calculation. You can add other parts for switching to other workbooks and in the workbook's Open event.

[davew]

Hi Kenneth

I've tried this- it doesn't work at all. Have you tried it? Doesn't the first part cancel the second part? Am I missing something?

... Hang on I think it's working now. I had the split code in the first two sheets which I think was conflicting. I'll check again.

[davew]

Brilliant- that works fine now.

I'd seen this previously but didn't understand the logic behind it. To a novice it does look like it cancels itself out- do you know how it works?

Thanks for your help.

Davew

[Teeroy]

The subs run on different events. One runs when you select the sheet tab to start using it and the other when you select another sheet tab to change sheets.

Just to be clear you understand the outcome, the calculation mode is an Application level setting. What Kenneth has given you is a means to turn it off when you enter a specific sheet and turn it on when you leave. While you are in that sheet NO automatic calculations will occur on any sheet in that workbook OR any other workbook that you have open.

[davew]

Hi Teeroy

Thanks for explaining that. As this works at application level I think this may not help me with multiple sheets. One of the problems I have is that as I amend a variable in one cell in one sheet it then triggers thousands of calc's in another sheet- which slows the process down. Is there a way around this?

[mikerickson]

Have you looked at Calculation Automatic except for Tables?

[Aflatoon]

The worksheets also have an enablecalculation property that you can set to False/True in code.

[davew]

Thanks all contributors. All of your suggestions have been fully explored by me. I've experimented using Aflatoon's 'enablecalculation' suggestion and this seems to do exactly what I need it to. I've combined it with Kenneth's methodology to give:
Sub OneTimeOnlyCalc()

Sheets("Sheet1").EnableCalculation = True

Sheets("Sheet1").EnableCalculation = False

End Sub