Turning Automatic Calculation On after Workbook loads

[dgunderson]

I've got a large workbook which continually calculates cells while web
queries and other macros are executed upon document load. I can
disable Automatic Calculation in Workbook_Open and the worksheet opens
very quickly. After loading the workbook, I can turn automatic
calculation back on, but I have users of this workbook that are
probably not capable of doing this on their own. What I would like to
do is find a trigger to turn automatic calculation back ON, but I'm
not sure what that is. I tried using Worksheet_Change, but unless I
type into the cell, it doesn't show it as changing. This is probably
easy, but I haven't found anything in the help files or in this forum.

Any help or ideas would be greatly appreciated.
Dale.

 

[Charles Chickering]

Here's a possibility:
in the workbook open event disable events then use ontime to turn them on
again:
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
Application.OnTime Now + TimeValue("00:00:05"), CalcAuto '5 second delay
End Sub

Sub CalcAuto
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Dave Peterson]

Excel picks up the calculation mode for the application from the first workbook
opened.

Your workbook_open code that changes calculation to manual isn't helping your
workbook--That event doesn't fire until the workbook is open--and excel has
already recalced (if it's in automatic mode) by that time.

You could provide them with two workbooks--the first changes calculation to
manual, then opens the second, then turns calculation to automatic, then closes
itself.

But I would think that as soon as the calculation is changed back to automatic,
the whole process would slow down just as much as when you opened the workbook
(with calculation set to automatic).

 

[dgunderson]

Works great Charles.

Thank you for your idea. I've got the timer set for longer since some
of our computers are slower and I don't want it to turn on calculation
in the middle of loading.

Thanks again.
Dale.

 

[dgunderson]

Thanks for the idea Dave, but we've got people that have enough
problems just opening one spreadsheet . In the future I might use
this technique in other applications.

Thanks again.
Dale.