Turning off a formula in cells or a sheet

[rossrc]

Can you turn off calculations in a Cell or an entire sheet using code?

I have a spreadsheet that calculates metrics for workers, and I want to use this same workbook to calculate metrics for managers.

The managers portion, however, uses Vlookups which really slow down the system. I'm looking for a way to turn off the manager calculations when checking on the workers metrics and turn them back on when we need to view the managers metrics. The managers calculations are all done on one specific sheet.

 

[TJH]

Hi

Perhaps you mean:

tools --> options --> calculation --> manual.

 

[rossrc]

Kind of

Hi

Perhaps you mean:

tools --> options --> calculation --> manual.

Well that's not exactly code, but I guess I'll try recording as a macro to see what the commands are. Thanks.

 

[rossrc]

Nope that didn't work. That works for the whole workbook and the users will have to keep clicking the f9 key when viewing the rest of the workbook.

What I'm really looking for is when a SHEET is unhidden then the calculations on the sheet start working. When the SHEET is hidden then the calculations turn off. Anyone know of code to do that?

 

[XLGibbs]

You can insert this in the Sheet necessary (change the number to apply), and some other code may be necessary depending on how you hide the sheets, and if this is user driven or not.
Sub CalcCheck()

If Sheets("Sheet1").Visible = False Then _
Sheets("Sheets1")..EnableCalculations = False

Else Sheets("Sheets1").EnableCalculations = True

End If

End Sub

 

[Nimrod]

Put the following code into the Project Module called "ThisWorkBook". :wink:

Then only the active sheet will have caculations being done automatically whenever there is a direct change in it.

Private Sub Workbook_Open()
Application.Calculation = xlManual
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ActiveSheet.Calculate
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlAutomatic
End Sub

EXPLAINATION
The OPen event turn caculations to Manual....
The Change of a value in a sheet triggers that specific sheet to be calc'd
The close event turns auto calcs back on for the appliation.