Full recalculation when opening a workbook

[Brian Hall]

I would like to trigger full re-calculation (equivalent to CTRL-ALT-F9
or Application.CalculateFull) whenever I load a new workbook.

I have Excel 97 SR-2, so CalculateFull is not available to me.

I am working on a project where at present I need to manually do a
CTRL-ALT-F9 whenever I open an existing sheet, so I would like to
automate this.

I have created an add-in with the following procedure defined in the
workbook vba

Private Sub Workbook_Open()
Application.SendKeys ("^%{F9}")
DoEvents
Debug.Print ("Sent keys")
End Sub

Now I can see the message printed so the code has been excuted, but
there is no effect on the sheet : I still need to manually do
CTRL-ALT-F9 before the sheet is in the state I require.

The question is: why didn't the sheet receive the message to
re-calculate and what can I do to fix this?

 

[Nigel]

Not sure if you can use the following in xl97 ?

Private Sub Workbook_Open()
Application.Calculate
End Sub

 

[Brian Hall]

No, that does not 'force' my sheet to recalculate. From xl2000 on there
is Application.CalculateFull, but my understanding is that CTRL-ALT-F9
is equivalent in xl97

 

[Charles Williams]

Hi Brian,

try this

Dim oSht as worksheet
Application.Calculation=xlCalculationManual

for each oSht in Worksheets
oSht.enablecalculation=false
osht.enablecalculation=true
next osht

Application.calculate


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Brian]

Actually, I believe now that my problem is not where I was looking for
it.

Remember that I want any .xls that is openned to be re-calculated once.
By putting the SUB Workbook_open procedure in my .xla I was effectively
telling Excel to recalculate only when the application openned (and
hence the add-in workbook openned too).

So, I now wonder: how do I put something in an add-in that will trap
the event of an .xls being openned? I guess that the
Application.WorkbookOpen event will be what I need.

Thanks for the help though!

 

[Rowan]

Hi Brian

See this article on setting up application level events. I don't know
for sure if this will work in 97 but it could be the answer to your
problem. If you implement application level events in your xla you can
then trap the workbook_open event and execute your sendkeys operation
everytime any workbook is opened.

http://www.cpearson.com/excel/AppEvent.htm

Hope this helps
Rowan

 

[Tom Ogilvy]

That article was written when xl97 was the latest version.