BeforeClose vs Auto_Close

[b_whittle]

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

 

[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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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.

 

[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

 

[b_whittle]

Tom, Paul,

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

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.

 

[b_whittle]

Bingo!

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

Ben

 

[Robert Bruce]

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]

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]

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]

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, (e-mail address removed) 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

On 2 Jun 2005 07:53:44 -0700, (e-mail address removed) 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"