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.

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???



Robots wrote:

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