How to stop calculation on open?

[G Lykos]

Greetings! Have an unwieldy workbook. It is set for manual calculation, no
calculation on save. I'd like to inhibit calculation when opening it as
well, but have been unable to intercept it using the various workbook and
application events. Any suggestions on how to do so?

Thanks,
George

 

[Dave Peterson]

Excel picks up that calculation for that session from the first workbook opened.

If you try to use any of that workbook's events, then you'll be too late.

You can change the calculation manually, then open your workbook--or use a
"dummy" workbook that changes calculation mode, then opens your real workbook.

Kind of like:

Option Explicit
Sub auto_open()
application.Calculation =xlCalculationManual
Workbooks.Open Filename:="c:\my documents\excel\book1.xls"
ThisWorkbook.Close savechanges:=False
End Sub

 

[Guest]

application.EnableEvents = False will do it

just execiute this statement before the workbook open
.... and of course execute
application.EnableEvents = True
when done

here is a quick extract of code where I use it to open all workbooks and
refresh DLL references

Application.EnableEvents = False
...
Set wb = Workbooks.Open(sPath & sFile, updatelinks:=0)
If Not wb Is Nothing Then
If wb.ReadOnly Then
rngScanned.Cells(lngRow, 6) = "Read-only"
Else
strResult = RefreshCOMLibraryReference(wb)
....
....
....


e.g.