Turning off a formula in cells or a sheet

rossrc

New Member
Joined
Jan 28, 2005
Messages
10
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

TJH

New Member
Joined
Mar 1, 2005
Messages
2
Hi

Perhaps you mean:

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

rossrc

New Member
Joined
Jan 28, 2005
Messages
10
Kind of

TJH said:
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

New Member
Joined
Jan 28, 2005
Messages
10
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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top