Workbook listed as VBA project after closing
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Workbook listed as VBA project after closing

  1. #1
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello all,

    Does this happen to anyone, 'cause it's happened to me. Some (a relative few, but enough to bug me) of my workbooks remain listed in the VBA Project Explorer even after I close them. It's odd, because just looking at the VBA PE, if you didn't otherwise know that the file was closed, you'd think it was open. When this occurs, I can view all the sheet objects and macros in the PE just as if the file was still open. Then, if I re-open the actual file (without rebooting inbetween), the file will appear listed twice in the PE.

    This has happened to me on different OS's (98, NT, etc), in Excel2000. It seems to happen with files that contain a Workbook_Open event, especially when that open procedure involves opening another Excel file in order to import data.

    Am I missing something obvious, maybe with my Close event, to convince Excel that it should now remove this file project from the Project Explorer? Any advice?

    Thanks!!!

    Tom Urtis

  2. #2
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    858
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    That is bizarre. I've never seen it.

    Do you mean that you can still view the macros, or just the list of modules?

    If you can still see the code in the macros, - what would happen if you change a macro and try to click on the Save icon in VBE? Do you get a horrific crash and burn? Does it appear to save? Or just nothing?

    What does the code look like in your Before_Close event?

    Bill

    _________________
    MrExcel.com Consulting

    [ This Message was edited by: MrExcel on 2002-03-15 08:17 ]

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried to duplicate your problem, but can't get it.

    Are the worksheets visible when you open them? i.e. are you instantiating an XL object using something like:



    Dim XL as object

    Set XL = CreateObject("Excel.Application")
    etc.


    (I don't expect that code to work since I just pulled it out of my *** and only use it as an example.)

    Or do you open these workbooks using something like:


    Workbooks.Open "C:TempBook1.xls"



    I had a similar problem to you a few years ago in Excel 97 SR-2 on Win NT4, but the problem "went away" when I upgraded to Excel 2000. (The fact that you're using 2000 makes me scratch my head some more).

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-03-15 08:24 ]

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom
    These workbooks haven't by any chance been
    used as a server ie. used to connect to
    the internet....??

    I know there can be issues when using IE


    Ivan

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Bill, Mark, and Ivan for the responses. Answers to your questions:

    Bill:
    When this happens, I can view the macro and "edit" it, but I've never had the nerve to save it in the VBE and risk a crash; frankly I've been too timid to try the experiment. What I have done, is to try running a macro from the file when I open it a second time, but (and this is really wierd) the macros do not run, nor are they listed in the Macro Name box where you'd ordinarily see them via Tools > Macro > Macros (Alt+F8). Though, the file project name is then listed twice in the VBE PE. So I close out Excel and re-open the file, but then the same problem persists. There's a follow up to this, 4 paragraphs down.

    My Before_Close events seem innocuous. Most of my clients have many end users who impose splits, or pane freezes, or gridlines for their own temporary viewing preference, but which annoys their colleagues if left that way after closing, so I usually involve a restoration of the windows' original fresh look for the next user to alter as they like, with an autosave.

    Mark:
    This has never happened while an XL object was created as you described. Sometimes my apps have hidden sheets, sometimes not.

    Ivan:
    Thanks, good idea, but unfortunately in my case I was not using Excel in that capacity.

    The good news is, this is a rare occurrence, having happened to me maybe half a dozen times over the years, but most recently last week on a job site. That time, the procedure was in a Workbook_Open event, and to work around the problem, I placed the code into a regular module, to be triggered by a Forms button, and deleted the Workbook_Open event altogether, as the procedure's purpose was to import data. I had no problems thereafter with the file, but it wasn't as sharp as it could've been, because the import was not truly automatic; it required a button click action from the user, which to me reduced the convenience factor for the client.

    This situation is not happening to me at the moment, but I'm being a little proactive today for if/when it does, and how to avoid it.

    Thanks again for your ideas; much appreciated. If I find a cause and/or solution, I'll be sure to post it in case anyone else has experienced this.

  6. #6
    New Member
    Join Date
    Dec 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook listed as VBA project after closing

    For me, this was caused by the "Acrobat PDFMaker Office COM Addin". Try deactivating that add-in and restarting Excel. This fixed that incredibly annoying issue for me. I was using Office 2007 or 2010.

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook listed as VBA project after closing

      
    Thank you, kirkjt, for that follow-up info...noted.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com