+ Reply to Thread
Results 1 to 17 of 17

BeforeClose vs Auto_Close

  1. #1
    arno
    Guest

    Re: BeforeClose vs Auto_Close

    Hi,
    > Auto_Close is soooooo last centuary ;-).


    correct

    you have to call another macro in the beforeclose-event like

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.Run ("test")
    End Sub

    BTW: Application.Run ("test") is also a kind medieval ages stuff

    arno


  2. #2
    Paul B
    Guest

    Re: BeforeClose vs Auto_Close

    Ben, do you have the beforeclose code in the thisworkbook module?
    --
    Paul B
    Always backup your data before trying something new
    Please post any response to the newsgroups so others can benefit from it
    Feedback on answers is always appreciated!
    Using Excel 2002 & 2003

    <[email protected]> wrote in message
    news:[email protected]...
    >I have a nagging issue. I am trying to run a BeforeClose sub but it
    > just won't go. However, if I replace it with the Auto_Close sub then it
    > works fine. The code is simple:
    >
    > private Sub Workbook_BeforeClose(Cancel As Boolean)
    > MsgBox "close macro is working"
    > end sub
    >
    > With the above code I do not see my message box when I close my
    > workbook. It just closes.
    >
    > private sub auto_close ()
    > MsgBox "close macro is working"
    > end sub
    >
    > With the above code I get my messagebox (and the useful bits of the
    > code which I haven't pasted in here). I read something about the need
    > for events to be enabled, so I ran a macro to enable events
    > (Application.EnableEvents = True), but to no avail.
    >
    > It's not a big issue because the Auto_Close does what I need, but as a
    > fickle follower of fashion I feel out of date. I've read that
    > Auto_Close is soooooo last centuary ;-). What could I be doing wrong
    > that stops the beforeclose working but lets auto_close do its job?
    >
    > regards Ben
    >




  3. #3

    BeforeClose vs Auto_Close

    I have a nagging issue. I am trying to run a BeforeClose sub but it
    just won't go. However, if I replace it with the Auto_Close sub then it
    works fine. The code is simple:

    private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "close macro is working"
    end sub

    With the above code I do not see my message box when I close my
    workbook. It just closes.

    private sub auto_close ()
    MsgBox "close macro is working"
    end sub

    With the above code I get my messagebox (and the useful bits of the
    code which I haven't pasted in here). I read something about the need
    for events to be enabled, so I ran a macro to enable events
    (Application.EnableEvents = True), but to no avail.

    It's not a big issue because the Auto_Close does what I need, but as a
    fickle follower of fashion I feel out of date. I've read that
    Auto_Close is soooooo last centuary ;-). What could I be doing wrong
    that stops the beforeclose working but lets auto_close do its job?

    regards Ben


  4. #4
    Tom Ogilvy
    Guest

    Re: BeforeClose vs Auto_Close

    Best to select it from the dropdowns in the top of the module (clear the
    module first) and let the declaration be entered by excel.

    then insert your

    msgbox "In Workbook_BeforeClose"

    between the prototype.

    That always works for me.

    Assumes you haven't disabled events or disabled macros.

    --
    Regards,
    Tom Ogilvy


    <[email protected]> wrote in message
    news:[email protected]...
    > Tom, Paul,
    >
    > I did not have the sub in the thisworkbook module, so I cut and pasted
    > it in. Still no joy though.
    >
    > Ben
    >




  5. #5

    Re: BeforeClose vs Auto_Close

    Bingo!

    Thanks a bunch, Tom. That was going to keep me up all night.

    Ben


  6. #6

    Re: BeforeClose vs Auto_Close

    Tom, Paul,

    I did not have the sub in the thisworkbook module, so I cut and pasted
    it in. Still no joy though.

    Ben


  7. #7
    arno
    Guest

    Re: BeforeClose vs Auto_Close

    Hi Tom,
    > I have never had to do that. Certainly not for the code the OP
    > showed.


    I remember that I could not make some larger code (subroutines etc etc)
    work in the open-event, but it worked when i called it as I described.
    I do not know why but then it worked.

    arno


  8. #8
    Bob Phillips
    Guest

    Re: BeforeClose vs Auto_Close

    "arno" <[email protected]> wrote in message
    news:[email protected]...
    >
    > you have to call another macro in the beforeclose-event like
    >
    > Private Sub Workbook_BeforeClose(Cancel As Boolean)
    > Application.Run ("test")
    > End Sub


    Inaccurate in two respects. You don't have to call a macro, you can embed
    the code in the event procedure. And you don't need application run, you
    could just use
    Test
    or
    Call Test.

    Application run is required to run a macro in an other workbook.



  9. #9
    Tom Ogilvy
    Guest

    Re: BeforeClose vs Auto_Close

    I have never had to do that. Certainly not for the code the OP showed.

    --
    Regards,
    Tom Ogilvy

    "arno" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > > Auto_Close is soooooo last centuary ;-).

    >
    > correct
    >
    > you have to call another macro in the beforeclose-event like
    >
    > Private Sub Workbook_BeforeClose(Cancel As Boolean)
    > Application.Run ("test")
    > End Sub
    >
    > BTW: Application.Run ("test") is also a kind medieval ages stuff
    >
    > arno
    >




  10. #10
    Tom Ogilvy
    Guest

    Re: BeforeClose vs Auto_Close

    is the BeforeClose sub in the ThisWorkbook module. If not, this might be
    the problem.

    --
    Regards,
    Tom Ogilvy


    <[email protected]> wrote in message
    news:[email protected]...
    > I have a nagging issue. I am trying to run a BeforeClose sub but it
    > just won't go. However, if I replace it with the Auto_Close sub then it
    > works fine. The code is simple:
    >
    > private Sub Workbook_BeforeClose(Cancel As Boolean)
    > MsgBox "close macro is working"
    > end sub
    >
    > With the above code I do not see my message box when I close my
    > workbook. It just closes.
    >
    > private sub auto_close ()
    > MsgBox "close macro is working"
    > end sub
    >
    > With the above code I get my messagebox (and the useful bits of the
    > code which I haven't pasted in here). I read something about the need
    > for events to be enabled, so I ran a macro to enable events
    > (Application.EnableEvents = True), but to no avail.
    >
    > It's not a big issue because the Auto_Close does what I need, but as a
    > fickle follower of fashion I feel out of date. I've read that
    > Auto_Close is soooooo last centuary ;-). What could I be doing wrong
    > that stops the beforeclose working but lets auto_close do its job?
    >
    > regards Ben
    >




  11. #11
    Robert Bruce
    Guest

    Re: BeforeClose vs Auto_Close

    Roedd <<Bob Phillips>> wedi ysgrifennu:

    >
    > Inaccurate in two respects.


    Inaccurate in three respects in total. Application.Run was never available
    in stand-alone VB. However, it was added to VB.NET, so it's very much 'this
    century'.

    --
    Rob

    http://www.asta51.dsl.pipex.com/webcam/

    This message is copyright Robert Bruce and intended
    for distribution only via NNTP.
    Dissemination via third party Web forums with the
    exception of Google Groups and Microsoft Communities
    is strictly prohibited and may result in legal action.



  12. #12
    Bob Phillips
    Guest

    Re: BeforeClose vs Auto_Close


    "Robert Bruce" <rob@analytical-dynamicsdotcodotukay> wrote in message
    news:[email protected]...

    > Inaccurate in three respects in total. Application.Run was never available
    > in stand-alone VB. However, it was added to VB.NET, so it's very much

    'this
    > century'.
    >


    <G>


    PS Is that Gaelic?



  13. #13
    Robert Bruce
    Guest

    Re: BeforeClose vs Auto_Close

    Roedd <<Bob Phillips>> wedi ysgrifennu:

    > PS Is that Gaelic?


    Very close. It's Welsh, the language of Heaven:
    http://www.bbc.co.uk/wales/storyofwelsh/

    --
    Rob

    http://www.asta51.dsl.pipex.com/webcam/

    This message is copyright Robert Bruce and intended
    for distribution only via NNTP.
    Dissemination via third party Web forums with the
    exception of Google Groups and Microsoft Communities
    is strictly prohibited and may result in legal action.



  14. #14
    Bob Phillips
    Guest

    Re: BeforeClose vs Auto_Close

    I didn't know which to ask. I thought I had seen somewhere that you were
    Welsh, but with a name like Robert Bruce As you can probably deduce from my
    name, my ancestry is also Welsh, but 2 generations back, so I am fully
    Anglicised.

    Thanks

    Bob

    "Robert Bruce" <rob@analytical-dynamicsdotcodotukay> wrote in message
    news:[email protected]...
    > Roedd <<Bob Phillips>> wedi ysgrifennu:
    >
    > > PS Is that Gaelic?

    >
    > Very close. It's Welsh, the language of Heaven:
    > http://www.bbc.co.uk/wales/storyofwelsh/
    >
    > --
    > Rob
    >
    > http://www.asta51.dsl.pipex.com/webcam/
    >
    > This message is copyright Robert Bruce and intended
    > for distribution only via NNTP.
    > Dissemination via third party Web forums with the
    > exception of Google Groups and Microsoft Communities
    > is strictly prohibited and may result in legal action.
    >
    >




  15. #15
    Robert Bruce
    Guest

    Re: BeforeClose vs Auto_Close

    Roedd <<Bob Phillips>> wedi ysgrifennu:

    > I didn't know which to ask. I thought I had seen somewhere that you
    > were Welsh, but with a name like Robert Bruce As you can probably
    > deduce from my name, my ancestry is also Welsh, but 2 generations
    > back, so I am fully Anglicised.


    I very nearly commented on your surname.

    Actually, I come from London, though my family background goes back to
    Southern Scotland originally. Further back, the name Bruce comes from Brix
    in Normandy from where the original Robert de Brix invaded England as part
    of the Conqueror's army. Even further back than that, the Normans came to
    Normandy from Scandinavia as 'Viking' invaders.

    I'm learing Welsh because my daughter goes to a Welsh-speaking school and I
    need to do all of the normal parent stuff like reading with her and helping
    with homework.

    None of which has got the slightest thing to do with programming Excel ;-)

    --
    Rob

    http://www.asta51.dsl.pipex.com/webcam/

    This message is copyright Robert Bruce and intended
    for distribution only via NNTP.
    Dissemination via third party Web forums with the
    exception of Google Groups and Microsoft Communities
    is strictly prohibited and may result in legal action.



  16. #16
    BBert
    Guest

    Re: BeforeClose vs Auto_Close

    On 2 Jun 2005 07:53:44 -0700, [email protected] wrote...

    [Workbook_BeforeClose, won't fire]
    > With the above code I do not see my message box when I close my
    > workbook.



    If you've got Excel 2000 but not SR-1 it won't fire. You had to use a
    sub named Auto_Close.

    Additional info: Event Procedures must be placed in the
    module for the object that they work with. E.g., Workbook_BeforeClose
    must be placed in the ThisWorkbook module, and the Worksheet_ events
    must be placed in the Sheet modules.

    See also:
    http://tinyurl.com/eysuf

    --
    Met vriendelijke groeten / Mit freundlichen Grüßen / With kind
    regards/Avec mes meilleures salutations
    BBert

    April 20, 1986
    Celtics (135) - Bulls (131)
    Larry Bird: "God disguised as Michael Jordan"

  17. #17
    Tom Ogilvy
    Guest

    Re: BeforeClose vs Auto_Close

    Just to point out, that that was a problem only for Addins as I recall.

    and it appears he got it working.

    --
    Regards,
    Tom Ogilvy

    "BBert" <[email protected]> wrote in message
    news:[email protected]...
    > On 2 Jun 2005 07:53:44 -0700, [email protected] wrote...
    >
    > [Workbook_BeforeClose, won't fire]
    > > With the above code I do not see my message box when I close my
    > > workbook.

    >
    >
    > If you've got Excel 2000 but not SR-1 it won't fire. You had to use a
    > sub named Auto_Close.
    >
    > Additional info: Event Procedures must be placed in the
    > module for the object that they work with. E.g., Workbook_BeforeClose
    > must be placed in the ThisWorkbook module, and the Worksheet_ events
    > must be placed in the Sheet modules.
    >
    > See also:
    > http://tinyurl.com/eysuf
    >
    > --
    > Met vriendelijke groeten / Mit freundlichen Grüßen / With kind
    > regards/Avec mes meilleures salutations
    > BBert
    >
    > April 20, 1986
    > Celtics (135) - Bulls (131)
    > Larry Bird: "God disguised as Michael Jordan"




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1