Don't want to recalculate all sheets

  • Thread starter Thread starter Clarkey
  • Start date Start date
C

Clarkey

Hi,

I have a workbook with a sheet containing a database. There are variou
other sheets in the workbook which are acting as reports from the mai
database. The problem is that the reports are fairly complex an
therefore recalculation is slow. I know you can turn off autocalc i
options, which is fine for the reports. However, the database contain
a few calculated fields which I need the users to see as they input th
data and therefore do not want to turn it off on this sheet.

There must be a way to just turn off the recalculation on certai
pages!?

Many thanks in advanc
 
This will toggle on/off whatever sheets you set up in the list below. Make sure
you do not change the order of where Sht1 appears as I have tried to ensure that
they cannot get out of sync in any way, ie it ends with Sht1.... = Not Sht1.....
It will do a full recalc when it put it back on, and will give you a message box
each time to tell you the status of the sheets.

Sub ToggleCalc()

Dim m As String
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim Sht3 As Worksheet
Dim Sht4 As Worksheet

Set Sht1 = Sheets("Sheet abc")
Set Sht2 = Sheets("Sheet def")
Set Sht3 = Sheets("Sheet ghi")
Set Sht4 = Sheets("Sheet jkl")

Sht2.EnableCalculation = Not Sht1.EnableCalculation
Sht3.EnableCalculation = Not Sht1.EnableCalculation
Sht4.EnableCalculation = Not Sht1.EnableCalculation
Sht1.EnableCalculation = Not Sht1.EnableCalculation

If Sht1.EnableCalculation = True Then
m = "ON"
Application.CalculateFull

Else: m = "OFF"
End If

MsgBox "You have turned Calculation Status <" & m & "> for the following
sheets:-" _
& vbCrLf & vbCrLf & Sht1.Name & vbCrLf & Sht2.Name & vbCrLf & Sht3.Name _
& vbCrLf & Sht4.Name

End Sub
 
Well it sort of is really, but you just need to be able to tap into it, for
which you need VBA :-)
 
Back
Top