Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 17th 04, 11:59 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2004
Posts: 3
Default VBA project does not close when workbook is closed

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 closing Excel and 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 since been corrected. The password prompts no
longer appear and workbooks are closed by the code without problems.

However, the problem I now have is that VBA projects now remain open for any
spreadsheet that is manually opened and closed (regardless of whether they
have any code in them). 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 and re-installed Excel but have been unable to
find a fix. I have found references to other people seeing similar symptoms,
but no solutions. My suspicion is that there may be some sort of hangover
issue in the registry or with DLLs, as all workbooks are affected.

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

Any assistance would be greatly appreciated.

Simon.

  #2   Report Post  
Old November 15th 04, 09:15 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2004
Posts: 1
Default VBA project does not close when workbook is closed

Simin, I have the same problem. All I have done is open another VBA project
which opens a form. When exiting, I get the password prompt problem. How
did you get rid of it. What objects need to be set to nothing?

regards
mark

"SimonN" wrote:

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 closing Excel and 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 since been corrected. The password prompts no
longer appear and workbooks are closed by the code without problems.

However, the problem I now have is that VBA projects now remain open for any
spreadsheet that is manually opened and closed (regardless of whether they
have any code in them). 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 and re-installed Excel but have been unable to
find a fix. I have found references to other people seeing similar symptoms,
but no solutions. My suspicion is that there may be some sort of hangover
issue in the registry or with DLLs, as all workbooks are affected.

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

Any assistance would be greatly appreciated.

Simon.

  #3   Report Post  
Old November 16th 04, 09:14 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2004
Posts: 3
Default VBA project does not close when workbook is closed

Mark,

Turns out the problem was specific to my development box - apparently
something goes wrong with Excel at some point (but no idea what or when!).
When I tried the same code on a different machine (with same version of
Office, Excel etc) it worked OK.

Hence I've rebuilt my development machine. Excel was already playing up as
projects always remained open after the associated workbook was closed (even
if they did not contain any VB!). Sadly he only way I have found to get rid
of this in the past has been a complete reformat and rebuild. Even
reinstalling Office did not cure it.

Not a very satisfactory solution I'm afraid.

Cheers
Simon.

"MarkRob" wrote:

Simin, I have the same problem. All I have done is open another VBA project
which opens a form. When exiting, I get the password prompt problem. How
did you get rid of it. What objects need to be set to nothing?

regards
mark

"SimonN" wrote:

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 closing Excel and 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 since been corrected. The password prompts no
longer appear and workbooks are closed by the code without problems.

However, the problem I now have is that VBA projects now remain open for any
spreadsheet that is manually opened and closed (regardless of whether they
have any code in them). 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 and re-installed Excel but have been unable to
find a fix. I have found references to other people seeing similar symptoms,
but no solutions. My suspicion is that there may be some sort of hangover
issue in the registry or with DLLs, as all workbooks are affected.

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

Any assistance would be greatly appreciated.

Simon.

  #4   Report Post  
Old November 16th 04, 11:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2004
Posts: 1
Default VBA project does not close when workbook is closed


It appears Google desktop is the culprit. I have removed it and m
problems have gone away. Did you have Google desktop on your proble
machine?
regards
Mar

--
markw
-----------------------------------------------------------------------
markww's Profile: http://www.excelforum.com/member.php...fo&userid=1646
View this thread: http://www.excelforum.com/showthread.php?threadid=27000

  #5   Report Post  
Old December 3rd 04, 11:28 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2004
Posts: 1,758
Default VBA project does not close when workbook is closed

Keep an eye out.

I don't use Google Desktop and I've see this same situation.

SimonN wrote:

Genius. I did have Google Desktop installed, and yes the problem went
away when I uninstalled it.

The weird thing is that I'm sure this problem didn't show up until I
made some code changes and then had a compile error. After that I got
the password prompt every time.

Frankly it would never have occurred to me to try this - thanks very
much. Many hours of diagnosis time saved!

Cheers
Simon.

markww wrote in message ...
It appears Google desktop is the culprit. I have removed it and my
problems have gone away. Did you have Google desktop on your problem
machine?
regards
Mark


--

Dave Peterson


  #6   Report Post  
Old July 1st 20, 05:47 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2020
Posts: 1
Default VBA project does not close when workbook is closed

I've created a new module for the VBA project that remained at project explorer even the workbook is closed. It works perfectly for me!

Sub closewb()

Application.Thisworkbook.Close

End Sub


On Monday, October 18, 2004 at 6:59:02 AM UTC+8, SimonN wrote:
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 closing Excel and 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 since been corrected. The password prompts no
longer appear and workbooks are closed by the code without problems.

However, the problem I now have is that VBA projects now remain open for any
spreadsheet that is manually opened and closed (regardless of whether they
have any code in them). 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 and re-installed Excel but have been unable to
find a fix. I have found references to other people seeing similar symptoms,
but no solutions. My suspicion is that there may be some sort of hangover
issue in the registry or with DLLs, as all workbooks are affected.

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

Any assistance would be greatly appreciated.

Simon.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Project - Open/Close with Macro Danny Excel Worksheet Functions 2 September 7th 07 11:02 PM
why do all files get closed when i only close one in excel? carrie Excel Discussion (Misc queries) 1 November 28th 05 06:45 PM
copy worksheet from closed workbook to active workbook using vba mango Excel Worksheet Functions 6 December 9th 04 07:55 AM
Project stays in VBEditor when workbook is closed Tod Excel Programming 1 April 7th 04 02:34 PM
VBA - on a button event, open another closed file, post changes, close file Fio Excel Programming 0 March 1st 04 01:08 PM


All times are GMT +1. The time now is 02:13 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017