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

Switching calculation to manual using Workbook_open
 

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

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

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

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

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

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