Switching calculation to manual using Workbook_open

[stuart_bisset]

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

 

[keepITcool]

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

 

[Dave Peterson]

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]

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 :

 

[stuart_bisset]

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

 

[Tom Ogilvy]

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

(e-mail address removed)

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.

 

[Peter T]

How about as you (KeepItCool) suggest but disabling from the Workbook Open
event, and re-enable when the Autpen macros are completed, eg

Sub wsCalc(bFlag As Boolean)
Dim ws As Worksheet
'False from Workbook_open event
'then True from the last Autpen 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