Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching calculation to manual using Workbook_open
I have a large workbook which takes a while to recalculate, hence one
of my workbook_open macros sets the calculation mode to xlCalculationManual. However if I already have a workbook open, which is set to xlCalculationAutomatic, then my large workbook performs a full calculation before running the workbook_open macros. I can interupt this by, say, pressing the CAPS LOCK key but whilst I know to do this other users of this model may not. Having searched other posts I found only 1 (somewhat cumbersome) solution which is to open a dummy workbook which contains a macro which switches calculation mode to manual then opens the large workbook then closes itself. I am not keen on this idea as it means issuing every user with 2 files. I tried replicating my pressing of CAPS LOCK, using the SENDKEYS command as the first statement in the workbook_open procedure but this didn't work. Can anyone help me out? How do I stop the model from calculating upon opening so that the workbook_open macros run immediately? TIA Stuart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching calculation to manual using Workbook_open
I don't think that this will work for the OP.
Stuart's problem with another workbook open (with calculation set to automatic) isn't addressed. And Stuart says that the recalc occurs before the auto open macros could run. (That's the way I remember it, too.) keepITcool wrote: Have you tried: to set the EnableCalculation on the worksheets to False (in BeforeSave event), then enabling it in workbook_open? Also a must read is: http://www.decisionmodels.com/calcsecretsc.htm -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote : I have a large workbook which takes a while to recalculate, hence one of my workbook_open macros sets the calculation mode to xlCalculationManual. However if I already have a workbook open, which is set to xlCalculationAutomatic, then my large workbook performs a full calculation before running the workbook_open macros. I can interupt this by, say, pressing the CAPS LOCK key but whilst I know to do this other users of this model may not. Having searched other posts I found only 1 (somewhat cumbersome) solution which is to open a dummy workbook which contains a macro which switches calculation mode to manual then opens the large workbook then closes itself. I am not keen on this idea as it means issuing every user with 2 files. I tried replicating my pressing of CAPS LOCK, using the SENDKEYS command as the first statement in the workbook_open procedure but this didn't work. Can anyone help me out? How do I stop the model from calculating upon opening so that the workbook_open macros run immediately? TIA Stuart -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching calculation to manual using Workbook_open
the idea was to prevent the workbook calculating when opened. but i checked my 'off the cuff' idea and it will not fly: enablecalculation is NOT saved with the workbook. thus setting it to false prior to save is useless -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Dave Peterson wrote : I don't think that this will work for the OP. Stuart's problem with another workbook open (with calculation set to automatic) isn't addressed. And Stuart says that the recalc occurs before the auto open macros could run. (That's the way I remember it, too.) keepITcool wrote: Have you tried: to set the EnableCalculation on the worksheets to False (in BeforeSave event), then enabling it in workbook_open? Also a must read is: http://www.decisionmodels.com/calcsecretsc.htm -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam wrote : I have a large workbook which takes a while to recalculate, hence one of my workbook_open macros sets the calculation mode to xlCalculationManual. However if I already have a workbook open, which is set to xlCalculationAutomatic, then my large workbook performs a full calculation before running the workbook_open macros. I can interupt this by, say, pressing the CAPS LOCK key but whilst I know to do this other users of this model may not. Having searched other posts I found only 1 (somewhat cumbersome) solution which is to open a dummy workbook which contains a macro which switches calculation mode to manual then opens the large workbook then closes itself. I am not keen on this idea as it means issuing every user with 2 files. I tried replicating my pressing of CAPS LOCK, using the SENDKEYS command as the first statement in the workbook_open procedure but this didn't work. Can anyone help me out? How do I stop the model from calculating upon opening so that the workbook_open macros run immediately? TIA Stuart |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching calculation to manual using Workbook_open
Thanks for your input guys. I guess this means it isn't possible. I
can only hope that someone from Microsoft spots these bugs and fixes them in future versions. I have always thought that the calculation mode should be a workbook specific setting and not an application setting. We should be able to have multiple workbooks open each with different calculation settings. Stuart |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching calculation to manual using Workbook_open
I doubt Microsoft views this type of thing as a bug, but in contrast, this
is a design decision. This is how the software was designed to operate. If you think a change to the design is warrented, then you need to voice your opinion at Other than that, it is just a wish and no one will hear it. An obvious, but not necessarily satisfactory solution is to use the beforeclose or beforesave event to make a static copy of the worksheets where you want to review the closing values. -- Regards, Tom Ogilvy wrote in message oups.com... Thanks for your input guys. I guess this means it isn't possible. I can only hope that someone from Microsoft spots these bugs and fixes them in future versions. I have always thought that the calculation mode should be a workbook specific setting and not an application setting. We should be able to have multiple workbooks open each with different calculation settings. Stuart |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching calculation to manual using Workbook_open
How about as you (KeepItCool) suggest but disabling from the Workbook Open
event, and re-enable when the Auto_Open macros are completed, eg Sub wsCalc(bFlag As Boolean) Dim ws As Worksheet 'False from Workbook_open event 'then True from the last Auto_Open macro For Each ws In Worksheets ws.EnableCalculation = bFlag Next End Sub Not sure if this'll make any difference but might be worth a try. Regards, Peter T "keepITcool" wrote in message t.com... the idea was to prevent the workbook calculating when opened. but i checked my 'off the cuff' idea and it will not fly: enablecalculation is NOT saved with the workbook. thus setting it to false prior to save is useless -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Dave Peterson wrote : I don't think that this will work for the OP. Stuart's problem with another workbook open (with calculation set to automatic) isn't addressed. And Stuart says that the recalc occurs before the auto open macros could run. (That's the way I remember it, too.) keepITcool wrote: Have you tried: to set the EnableCalculation on the worksheets to False (in BeforeSave event), then enabling it in workbook_open? Also a must read is: http://www.decisionmodels.com/calcsecretsc.htm -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam wrote : I have a large workbook which takes a while to recalculate, hence one of my workbook_open macros sets the calculation mode to xlCalculationManual. However if I already have a workbook open, which is set to xlCalculationAutomatic, then my large workbook performs a full calculation before running the workbook_open macros. I can interupt this by, say, pressing the CAPS LOCK key but whilst I know to do this other users of this model may not. Having searched other posts I found only 1 (somewhat cumbersome) solution which is to open a dummy workbook which contains a macro which switches calculation mode to manual then opens the large workbook then closes itself. I am not keen on this idea as it means issuing every user with 2 files. I tried replicating my pressing of CAPS LOCK, using the SENDKEYS command as the first statement in the workbook_open procedure but this didn't work. Can anyone help me out? How do I stop the model from calculating upon opening so that the workbook_open macros run immediately? TIA Stuart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Manual vs Automatic | Excel Discussion (Misc queries) | |||
Manual Calculation | Setting up and Configuration of Excel | |||
Automatic Calculation switching to manual | Excel Discussion (Misc queries) | |||
Calculation set as Manual | Excel Discussion (Misc queries) | |||
Manual / Automatic Calculation | Excel Programming |