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

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

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

--
Regards,
Tom Ogilvy

"arno" wrote in message
...
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

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

"arno" wrote in message
...

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.

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.

"Robert Bruce" <rob@analytical-dynamicsdotcodotukay wrote in message
...

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?

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.

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

wrote in message
ups.com...
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

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

--
Regards,
Tom Ogilvy


wrote in message
ups.com...
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, Paul,

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

Ben

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


wrote in message
ups.com...
Tom, Paul,

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

Ben

Bingo!

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

Ben

On 2 Jun 2005 07:53:44 -0700, 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"

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" wrote in message
e.nl...
On 2 Jun 2005 07:53:44 -0700, 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"