VBA project does not close when workbook is closed

[Simon Norris]

I have a large project that uses VBA to open and close files. The project
is password protected. I recently ran into a problem whereby a password
prompt appears when Excel is closed and any VBA projects remain open after
the corresponding workbooks have been closed.

This error was apparently caused by omitting statements in the style of 'Set
objectName to Nothing' before closing the workbook (a solution documented in
other forums). This has now been corrected. The password prompts no longer
appear and workbooks are closed by the code without any problem.

However, the issue I now have is that VBA projects now remain open for *any*
spreadsheet that is manually opened and closed (regardless of whether it has
any code in it). Re-opening the same file causes a second instance of the
same project to appear. They remain open until Excel is shut down.

I have tried cleaning my code, re-installed Excel and reset various Registry
parameters but have been unable to find a fix. I have found several
references to other people seeing the same symptoms, but no solutions
(beyond the 'set to nothing' fix I have already implemented). My suspicion
is that there may be some sort of hangover issue in the registry or with
DLLs, since all workbooks are affected.

I am using Excel 2002 with SP3 (although identical symptoms have been noted
on a colleague's machine which runs Excel 2003).

Any assistance would be greatly appreciated (this is my first posting to
this forum).

Simon
London

 

[jboden]

Have you gotten any response on this issue or found a fix?

I am having the same problems.
-Using VB.NET I create one instance of Excel.
-Open a workbook from VB code.
-Close the workbook from VB code.
-Open a workbook from VB code.
-Then view the VBA editor and the VBAproject from the first workbook is
still open.

Any help would be great.
Thanks
Jay