Workbook_open not running and Phantom Projects

[chuckm]

I recently discovered that I have the problem of workbook_open not
firing upon subsequent openings of my workbook....e.g. start excel and
open workbook...workbook_open runs fine. Close workbook and re-open
it, workbook_open does not run. I also discovered that when this
happens I have the phantom project problem....e.g. VBA project does
not close when workbook is closed and a 2nd project is opened when the
workbook is re-opened.

I have searched the forum and read most of everything on these two
issues. I believe they are related and the workbook_open not
running is probably caused by the project not closing. Problem is I
still don't know what to do about it.

This happens for an app that I have been working on and enhancing for
the past few years. Happens on 2 separate computers running XP with
excel 2003 and 2000.

Now, I can create a new workbook and put a msgbox in the workbook_open
and it works all the time.

I have tested my app through all the dev versions and found the
version where this starts happening....e.g., problem does not happen
for all versions earlier than this and happens for all versions
thereafter.

The differences between the good version and bad version are
small.....just 3k diff in file size and about 40 lines of bland VB
code.

All changes were contained in 2 different modules (23 modules in
total). If I swap the modules the problem does not follow it. If I
remove these modules entirely the problem does not go away. So, I
started removing modules 1 by 1 and found a module whose presence
seems to cause the problem....e.g. once that particular module was
removed, the problem went away. But, this module is identical to
the one in previous versions!

The workbook_open just makes a sheet current, reads data from a cell
and then displays a splash screen/userform. nothing fancy or
tricky.

So, I am stumped and don;t know where to go. As others have
reported, If Excel is closed and restarted, the problem is
cleared.....But I'd like to solve it and seeing that I have 2 very
similar workbooks it seems that I should be able to find out what is
causing it.

Can anyone out there give me some guidance here? At this point I
don't know what to look for.
thanks
chuck

 

[Joel]

Instead of closing the workbook at the end of the macro save the workboo and
then do an apllication.quit. The close may not be shuting down the
application the quit should kill the application.

 

[Peter T]

If you've read everything about Phantom projects (I think I may have coined
the term) you will have read Jon Peltier's conclusion that the issue may
linked to certain third party VSTO addins.

I doubt the failure wb Open events is directly related, though conceivably
the same addin (if you have one) is responsible both for the phantom
projects and for disabling app' events, albeit for different reasons.
However you also say the problem has only occurred since modifying your own
code so maybe suspicion lays there.

Quick thing to try, in the immediate window (ctrl-g), paste
?application.EnableEvents

Before and after opening a workbook that you expect an open event to run,
place the cursor at the end of the line and hit enter.

If EnableEvents are enabled before and after the open event really should
have fired. Maybe for some reason the code terminates prematurely giving the
impression it failed, add the following debug lines in the open event

' start of code
debug.? thisworkbook.name, "open: start"
'code
' end of code
debug.? thisworkbook.name, "open: all done"
End Sub

Regards,
Peter T

 

[chuckm]

If you've read everything about Phantom projects (I think I may have coined
the term) you will have read Jon Peltier's conclusion that the issue may
linked to certain third party VSTO addins.

I doubt the failure wb Open events is directly related, though conceivably
the same addin (if you have one) is responsible both for the phantom
projects and for disabling app' events, albeit for different reasons.
However you also say the problem has only occurred since modifying your own
code so maybe suspicion lays there.

Quick thing to try, in the immediate window (ctrl-g), paste
?application.EnableEvents

Before and after opening a workbook that you expect an open event to run,
place the cursor at the end of the line and hit enter.

If EnableEvents are enabled before and after the open event really should
have fired. Maybe for some reason the code terminates prematurely giving the
impression it failed, add the following debug lines in the open event

' start of code
debug.? thisworkbook.name, "open: start"
'code
' end of code
debug.? thisworkbook.name, "open: all done"
End Sub

Regards,
Peter T

Ok...I've done some more digging into the problem
It now seems that teh root of the problem is when I introduced a call
to a routine in workbook_open that basically executes a
Application.EnableEvents = False. This was done to enable some code
in the sheet to execute that had to do with row highlighting and
cursor placement.

so, I bring up excel and the workbook opens fine. Close the workbook
and project is still open. Open workbook again, workbook_open doen
not execute and phantom project is open.

Now, if I issue a Application.EnableEvents = True, the phantom project
goes away. I can then close the workbook, re-open and it works ok.

Seems that I need to issue Application.EnableEvents = True from a
workbook_close..... if such a thing exists.

So, seems that the Workbook_open and Phantom projects problem are
related...at least in my case.

Now...is there a Workbook_close...or something like that that runs
when you close the workbook?

thanks everyone for you help and guidance
chuck

 

[Peter T]

Not sure I quite follow but from what you say it sounds like it's your code
that disables events and fails to reset them.

If that's the case, you really should reset the events as your code is about
to terminate, if necessary from within an error handler.

Indeed there is a Workbook close event but if events are disabled the close
event won't fire. As a final escape when all else fails try this in a normal
module

Sub auto_close()
application.enable.events = true
end sub

This and auto_open are not impacted by events, maybe use same for your open
event. Of course best thing is make sure you reset events.

Regards,
Peter T

 

[chuckm]

Not sure I quite follow but from what you say it sounds like it's your code
that disables events and fails to reset them.

If that's the case, you really should reset the events as your code is about
to terminate, if necessary from within an error handler.

Indeed there is a Workbook close event but if events are disabled the close
event won't fire. As a final escape when all else fails try this in a normal
module

Sub auto_close()
application.enable.events = true
end sub

This and auto_open are not impacted by events, maybe use same for your open
event. Of course best thing is make sure you reset events.

Regards,
Peter T

Thanks Peter....

I added Workbook_beforeClose and auto_close....both with
Application.EnableEvents = True.

That has solved my problems.

appreciate it
chuck

 

[Peter T]

I added Workbook_beforeClose and auto_close....both with

Application.EnableEvents = True.

That has solved my problems.

It may have merely masked your problems for the time being. As I mentioned
before, you really should ensure you reset EnableEvents after disabling them

Regards,
Peter T