Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
F9 Manual Recalculate only for one workbook
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
F9 Manual Recalculate only for one workbook
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. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tenacity9" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
F9 Manual Recalculate only for one workbook
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 -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tenacity9" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
F9 Manual Recalculate only for one workbook
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" wrote: 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 -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tenacity9" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
F9 Manual Recalculate only for one workbook
Not that I'm aware of. If I'm wrong, I'm sure someone will chime in.
Barb Reinhardt "Tenacity9" wrote: 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" wrote: 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 -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tenacity9" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
F9 Manual Recalculate only for one workbook
Depending on the version of excel you're using, each worksheet can have its
calculation set via code. workbooks("someworkbook.xls").worksheets("someshee t").EnableCalculation = false xl2003 has this feature. I'm not sure if it was added in xl2k or xl2003, though. Tenacity9 wrote: 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" wrote: 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 -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tenacity9" wrote: 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. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
F9 Manual Recalculate only for one workbook
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" wrote: Depending on the version of excel you're using, each worksheet can have its calculation set via code. workbooks("someworkbook.xls").worksheets("someshee t").EnableCalculation = false xl2003 has this feature. I'm not sure if it was added in xl2k or xl2003, though. Tenacity9 wrote: 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" wrote: 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 -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tenacity9" wrote: 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. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
F9 Manual Recalculate only for one workbook
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. Tenacity9 wrote: 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" wrote: Depending on the version of excel you're using, each worksheet can have its calculation set via code. workbooks("someworkbook.xls").worksheets("someshee t").EnableCalculation = false xl2003 has this feature. I'm not sure if it was added in xl2k or xl2003, though. Tenacity9 wrote: 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" wrote: 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 -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Tenacity9" wrote: 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. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Very long workbook computation time (F9 manual compute) | Excel Discussion (Misc queries) | |||
Recalculate changes only | Excel Discussion (Misc queries) | |||
Opening Workbook resets calculation to automatic from manual etc | Excel Discussion (Misc queries) | |||
Won't recalculate | Excel Discussion (Misc queries) | |||
will not recalculate | Excel Worksheet Functions |