Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    trap excel application quit (Excel2000)

    I'm having one of those days when I think I'm losing my mind.
    In Word2000 I have a class module that traps the Word application quit event. Fine, thank you for asking.
    <pre>Private Sub WDApp_Quit()
    Call AMLog("WDApp_Quit")
    End Sub</pre>

    In Excel2000 I'd like to have a class module that traps the Excel application quit event, but it doesn't show up on my radar; nor in Excel2003 as far as I can see.

    Might someone confirm that there is indeed such a disparity between versions?

    A search of all forums and Google/VBA turns up a great many posts on how to issue the Application.Quit, but none that I can see on how to trap the event.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: trap excel application quit (Excel2000)

    If you look up the Application object in the Word VBA help, and click on Events, you'll see Quit listed.
    If you look up the Application object in the Excel VBA help and click on Events, you will *not* see anything related to Quit, only events related to workbooks and worksheets.

    A rather clunky workaround is to place a macro named Auto_Close in a standard module in your Personal.xls. Since Personal.xls is normally opened when you start Excel and closed when you quit it, the Auto_Close macro will be run when Excel is quit. (For some reason, the Workbook_Close event doesn't occur for Personal.xls)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trap excel application quit (Excel2000)

    Thanks Hans, I thought I was going crazy. Even by Excel 2003 there appears to be no quit event (sigh!)

    Personal.xls will not be an option for me/us as the workbook is to be distributed across the client site.
    Thanks again

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: trap excel application quit (Excel2000)

    You could use an add-in (.xla). Both the Workbook_BeforeClose event and a macro named Auto_Close are executed when the user quits Excel with the add-in installed. An add-in can easily be distributed.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trap excel application quit (Excel2000)

    > You could use an add-in (.xla).
    I've got one of those here somewhere, UX.XLA from memory (pats pockets ...)
    Thanks for the suggestion. I'll give it a try and get back to you.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trap excel application quit (Excel2000)

    > Both the Workbook_BeforeClose event and a macro named Auto_Close are executed

    Unless I've done something horribly wrong, the Auto_Close seems to take precedence and lock out the Workbook_BeforeClose event.
    When I disable the Auto_Close, the Workbook_BeforeClose event takes centre stage.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: trap excel application quit (Excel2000)

    When I install your lib.xla as an add-in in Excel, and quit, I first see the message boxes from the Workbook_BeforeClose event procedure, then the message box from the Auto_Close macro.
    This is in Excel 2002 SP3.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: trap excel application quit (Excel2000)

    I also tested in Excel 2003 SP2. Same result: the message boxes from Workbook_BeforeClose and Auto_Close are both displayed (in that order).

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trap excel application quit (Excel2000)

    > I also tested in Excel 2003 SP2.
    Thank you.
    I have just tested it in 2002 on my Big Beige Box and see both "events".
    I must have something weird going on on both my laptop and the client's desktop.
    (Besides me, I mean.)

Posting Permissions

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