F9 Manual Recalculate only for one workbook

[Tenacity9]

Setting Tools-Options-Calculation to manual (ie, have to use F9) changes the
preference for all open workbooks in Excel 2003.

If you only want manual recalculation to apply to a particular workbook
permanently (in other words until the preference is changed back to
automatic), but other open workbooks and all new workbooks to be calculated
automatically (ie, no F9), is there a way to accomplish this easily?

Thanks.

 

[Barb Reinhardt]

You may want to add a Workbook_Open event that turns off Calculation and a
Workbook_CLose event that turns it back on. Of course, if you have other
workbooks open at the same time, you won't be able to do any calculations
unless you do them manually.

 

[Barb Reinhardt]

Press Alt F11.

Select the ThisWorkbook module for the VBA project your editing.

Paste this in.

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

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

 

[Tenacity9]

Thank you, this is somewhat helpful.

However, "Of course, if you have other
workbooks open at the same time, you won't be able to do any calculations
unless you do them manually." unfortunately reduces effectiveness, since I
usually have the worksheet in question open.

Isn't there a way through VBA or otherwise to restrict F9 only to one (or
several) chosen workbooks, while not "polluting" the other non-chosen
workbooks?

Thanks.

 

[Barb Reinhardt]

Not that I'm aware of. If I'm wrong, I'm sure someone will chime in.

Barb Reinhardt

 

[Dave Peterson]

Depending on the version of excel you're using, each worksheet can have its
calculation set via code.

workbooks("someworkbook.xls").worksheets("somesheet").EnableCalculation = false

xl2003 has this feature. I'm not sure if it was added in xl2k or xl2003,
though.

 

[Tenacity9]

Thanks.

Using Excel 2003.

Could you please write the entire VBA entry soup to nuts, so I can cut and
paste. I'm far from an expert at VBA. In other words, the code to put into
the workbook/worksheet I want to have manual F9 recalculation. Then I can
leave Tools-Options on Automatic for all other workbooks.

Appreciate it.

 

[Dave Peterson]

You can use these two subroutines.

Option Explicit
Sub TurnOffCalc()

Dim wkbk As Workbook
Dim wks As Worksheet

For Each wkbk In Application.Workbooks
If wkbk.Name = ThisWorkbook.Name Then
'do nothing
Else
For Each wks In wkbk.Worksheets
wks.EnableCalculation = False
Next wks
End If
Next wkbk
End Sub

Sub TurnOnCalc()

Dim wkbk As Workbook
Dim wks As Worksheet

For Each wkbk In Application.Workbooks
If wkbk.Name = ThisWorkbook.Name Then
'do nothing
Else
For Each wks In wkbk.Worksheets
wks.EnableCalculation = True
Next wks
End If
Next wkbk
End Sub

Open your workbook and hit alt-f11.

Then choose the macro you want to run.