R
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.
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.