BeforeClose vs Auto_Close

[arno]

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

[Paul B]

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
>

[[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

[Tom Ogilvy]

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
>

[[email protected]]

Bingo!

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

Ben

[[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

[arno]

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

[Bob Phillips]

"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.

[Tom Ogilvy]

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
>

[Tom Ogilvy]

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
>

[Robert Bruce]

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.

[Bob Phillips]

"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?

[Robert Bruce]

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.

[Bob Phillips]

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.
>
>

[Robert Bruce]

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.

[BBert]

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"

[Tom Ogilvy]

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"