Workbook Open event does not fire

[Robots]

There have been many threads discussing the problem that sometimes,
mysteriously, the workbook open event does not fire, despite
EnableEvents = True.

I have encountered this frustrating problem myself on numerous
occasions and have concluded it is an Excel bug which, even in the
latest version, has not been resolved.

I rememeber reading an MSDN article (but cannot now find) recommending
using the old Auto_Open subroutine. Sometimes this works, sometimes
it doesn't.

I have wasted countless hours trying to isolate the problem.
Sometimes it appears to be resolved and then it's back again! Aaah!
Actually if you have other workbooks open, the problem can propogate
to them as well. As soon as you close the offending workbook, all is
back to normal!

I can't guarantee that the following will work in all circumstances
but when I struck the problem again recently, I seemed to resolve it
by setting the workbook to manual calculation (via the menu, not VBA).
To overcome the irritation of having to constantly press F9, I added
this event procedure in the Workbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
Application.Calculate
End Sub

I'd be interested in feedback whether this works for others. The
solution suggests that VBA sometimes gets confused when opening a
workbook and trying to recalculate at the same time.

 

[Dave Peterson]

I've never experienced the problem, but I've seen posts that do this kind of
thing:

Private Sub Workbook_Open()
Application.OnTime Now, "Continue_Open"
End Sub

Then in a General module,

sub Continue_Open()
'your real code here
end sub

I guess the thought is to let excel do some housekeeping and get caught up.

And if you're opening that workbook with the workbook_open code via a macro and
that macro is called from a shortcut key---And that shortcut key includes the
Shift key, then this can cause trouble.

When you hold down the shift key while you're opening a workbook (manually),
then excel doesn't run the auto_open or workbook_open code.

And the shiftkey in the shortcut key can confuse excel so that it doesn't run
the code.

Maybe sometimes you'd run the code to open the problem workbook via
Tools|macro|macros... and sometimes through a shortcut key???

 

[Ian Robinson]

I've seen that proposed solution before but it misunderstands the nature
of the problem - unless the proponents have a different problem, of
course!.

Since the Workbook_Open event does not fire (or appear to fire) in the
first place, the procedure Sub workbook_Open() ... End Sub is never
entered.

In other words, it does not matter what code you put inside the Sub (eg
merely MsgBox "Hello" or just Stop etc) it will not be run because VBA
never gets a chance to.

An alternative (and perhaps better) workwround to my previous workaround
is to use code like this:

Option Explicit
Public iCalculationMode As Integer

Private Sub Workbook_Activate()
iCalculationMode = Application.Calculation
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub Workbook_Deactivate()
Application.Calculation = iCalculationMode
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
ThisWorkbook.Saved = True
End Sub

This method avoids the need to set manual calculation via the menu bar
and provides for other workbooks that might be open at the time.

At one time I thought the problem was associated with UDFs that may
exist and need calculation on startup but the pattern was never
consistent. At other times, using Auto_Open worked, but not always
(even that would not fire).

If you trawl the MSDN Knowledge Base, it appears that Microsoft are
aware that there is a problem but never offer a cause or solution other
than suggesting that using Auto_Open *might* work.

 

[Dave Peterson]

In the first part of my response, I was guessing that the workbook_open event
did indeed start--but it stopped before it did anything the user could notice.
(I don't know what code the OP was using or how he knew that the event didn't
fire.)