Can i make some worksheets auto and others manual calculation

  • Thread starter Thread starter scott23
  • Start date Start date
S

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
 
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.
 
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)
 
Back
Top