Can i make some worksheets auto and others manual calculation

[scott23]

Hi folks,
My workbook has a large amount of formulas and im obviously in need of
access, but i dont know how to integrate it with my .xls stuff.

However, in the interim is there a way to make some of my worksheets
automatic calculation and yet others manual where F9 will update them
? As it currently stands, the option in excel is global for the whole
workbook.

Thanks
sg

 

[Ken Wright]

If you are happy to use VBA yes.

This will toggle calc status for individual sheets and tell you which ones you
have turned on/off each time - just put in the correct sheetnames. If you want
to expand the number of sheets listed, then make sure you keep to the order
shown, with sht1... = not sht1.... being the LAST one. This should ensure they
do not end up out of sync at all.


Sub ToggleCalc()

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

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

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

If Sht1.EnableCalculation = True Then
m = "ON"
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

End Sub


As for your workbook, it may or may not be an access candidate, but I would
certainly explore ways of making what you have as efficient as possible before
necessarily jumping off into another app.

 

[Bob Phillips]

No, not possible Calculation is an application property, not just the
workbook.

What you can do is make it manual, and have button s to do sheet calculate.
This is the sort of macro you would need to tie to the button

Sub CalcSheet()
ActiveSheet.Calculate
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)