Hi,

Could someone please explain the following behaviour?

The Workbook_BeforeClose event is triggered after the ThisWorkbook.Close
method is called and all lines in the Event sub are executed but the status
bar never actually changes its text, and no exceptions are thrown either.

Add this to the ThisWorkbook module and test:

Sub RunThisMacro()
Application.StatusBar = Rnd
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = "THIS DOES NOT WORK" 'this is executed but it
doesn't actually do anything.
End Sub

Thanks for your insight.

Leo

Hi Leo,

It doesn't work for me either. There seem to be a number of things that do
not work in the BeforeClose event. Try this instead -

Sub auto_close()
Application.StatusBar = "Bye"
' or
' Application.StatusBar = False
End Sub

Regards,
Peter T

"LEO@KCC" wrote in message
...
Hi,

Could someone please explain the following behaviour?

The Workbook_BeforeClose event is triggered after the ThisWorkbook.Close
method is called and all lines in the Event sub are executed but the
status
bar never actually changes its text, and no exceptions are thrown either.

Add this to the ThisWorkbook module and test:

Sub RunThisMacro()
Application.StatusBar = Rnd
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = "THIS DOES NOT WORK" 'this is executed but it
doesn't actually do anything.
End Sub

Thanks for your insight.

Leo

Hi Peter, The Auto_Close sub is not executed when the Close method of
ThisWorkbook is called (this is in the Help documentation), so I still have
the problem...

Regards,

Leo T


"Peter T" <peter_t@discussions wrote in message
...
Hi Leo,

It doesn't work for me either. There seem to be a number of things that do
not work in the BeforeClose event. Try this instead -

Sub auto_close()
Application.StatusBar = "Bye"
' or
' Application.StatusBar = False
End Sub

Regards,
Peter T

"LEO@KCC" wrote in message
...
Hi,

Could someone please explain the following behaviour?

The Workbook_BeforeClose event is triggered after the ThisWorkbook.Close
method is called and all lines in the Event sub are executed but the
status
bar never actually changes its text, and no exceptions are thrown either.

Add this to the ThisWorkbook module and test:

Sub RunThisMacro()
Application.StatusBar = Rnd
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = "THIS DOES NOT WORK" 'this is executed but it
doesn't actually do anything.
End Sub

Thanks for your insight.

Leo

Hmmm, you're right, I forgot about that.

Things I tried in the BeforeClose event -

1.
Call another macro to run the code to clear the statusbar. Result the macro
is called OK but the statusbar code doesn't work

2.
if len(application.statusbar) then
Cancel = true
Application.OnTime Now, "ReClose"
end if

' in a normal module
Sub ReClose()
Application.StatusBar = False
ThisWorkbook.Close
end sub

Result - the ontime macro is not called at all, also the wb does not close
due to Cancel = true

3. This works for me, not sure why

again in the BeforeClose event -

ThisWorkbook.RunAutoMacros xlAutoClose

Sub auto_close() as posted previously, ie code to set or clear the
statusbar.

Indeed strange, but as I say the above appears to work for me.

Regards,
Peter T




"LEO@KCC" wrote in message
...
Hi Peter, The Auto_Close sub is not executed when the Close method of
ThisWorkbook is called (this is in the Help documentation), so I still
have
the problem...

Regards,

Leo T


"Peter T" <peter_t@discussions wrote in message
...
Hi Leo,

It doesn't work for me either. There seem to be a number of things that
do
not work in the BeforeClose event. Try this instead -

Sub auto_close()
Application.StatusBar = "Bye"
' or
' Application.StatusBar = False
End Sub

Regards,
Peter T

"LEO@KCC" wrote in message
...
Hi,

Could someone please explain the following behaviour?

The Workbook_BeforeClose event is triggered after the
ThisWorkbook.Close
method is called and all lines in the Event sub are executed but the
status
bar never actually changes its text, and no exceptions are thrown
either.

Add this to the ThisWorkbook module and test:

Sub RunThisMacro()
Application.StatusBar = Rnd
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = "THIS DOES NOT WORK" 'this is executed but it
doesn't actually do anything.
End Sub

Thanks for your insight.

Leo

Obviously simplest would be to include code to 'do' the statusbar just
before calling code to close the wb, though I assume that's not viable.

But almost as simple would be to place the code to 'do' the statusbar in the
workbook deactivate event, possibly only called subject to a module level
boolean flag that's set in the Close event.

Peter T

The code does

"Peter T" <peter_t@discussions wrote in message
...
Hmmm, you're right, I forgot about that.

Things I tried in the BeforeClose event -

1.
Call another macro to run the code to clear the statusbar. Result the
macro
is called OK but the statusbar code doesn't work

2.
if len(application.statusbar) then
Cancel = true
Application.OnTime Now, "ReClose"
end if

' in a normal module
Sub ReClose()
Application.StatusBar = False
ThisWorkbook.Close
end sub

Result - the ontime macro is not called at all, also the wb does not close
due to Cancel = true

3. This works for me, not sure why

again in the BeforeClose event -

ThisWorkbook.RunAutoMacros xlAutoClose

Sub auto_close() as posted previously, ie code to set or clear the
statusbar.

Indeed strange, but as I say the above appears to work for me.

Regards,
Peter T




"LEO@KCC" wrote in message
...
Hi Peter, The Auto_Close sub is not executed when the Close method of
ThisWorkbook is called (this is in the Help documentation), so I still
have
the problem...

Regards,

Leo T


"Peter T" <peter_t@discussions wrote in message
...
Hi Leo,

It doesn't work for me either. There seem to be a number of things
that
do
not work in the BeforeClose event. Try this instead -

Sub auto_close()
Application.StatusBar = "Bye"
' or
' Application.StatusBar = False
End Sub

Regards,
Peter T

"LEO@KCC" wrote in message
...
Hi,

Could someone please explain the following behaviour?

The Workbook_BeforeClose event is triggered after the
ThisWorkbook.Close
method is called and all lines in the Event sub are executed but the
status
bar never actually changes its text, and no exceptions are thrown
either.

Add this to the ThisWorkbook module and test:

Sub RunThisMacro()
Application.StatusBar = Rnd
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = "THIS DOES NOT WORK" 'this is executed but it
doesn't actually do anything.
End Sub

Thanks for your insight.

Leo

Peter, some good ideas there. Thanks.

I think that there may be something that "locks" the statusbar after calling
the Close method. Very wierd.

Leo T

--
Regards,

Leo T
"Peter T" <peter_t@discussions wrote in message
...
Obviously simplest would be to include code to 'do' the statusbar just
before calling code to close the wb, though I assume that's not viable.

But almost as simple would be to place the code to 'do' the statusbar in
the
workbook deactivate event, possibly only called subject to a module level
boolean flag that's set in the Close event.

Peter T

The code does

"Peter T" <peter_t@discussions wrote in message
...
Hmmm, you're right, I forgot about that.

Things I tried in the BeforeClose event -

1.
Call another macro to run the code to clear the statusbar. Result the
macro
is called OK but the statusbar code doesn't work

2.
if len(application.statusbar) then
Cancel = true
Application.OnTime Now, "ReClose"
end if

' in a normal module
Sub ReClose()
Application.StatusBar = False
ThisWorkbook.Close
end sub

Result - the ontime macro is not called at all, also the wb does not
close
due to Cancel = true

3. This works for me, not sure why

again in the BeforeClose event -

ThisWorkbook.RunAutoMacros xlAutoClose

Sub auto_close() as posted previously, ie code to set or clear the
statusbar.

Indeed strange, but as I say the above appears to work for me.

Regards,
Peter T




"LEO@KCC" wrote in message
...
Hi Peter, The Auto_Close sub is not executed when the Close method of
ThisWorkbook is called (this is in the Help documentation), so I still
have
the problem...

Regards,

Leo T


"Peter T" <peter_t@discussions wrote in message
...
Hi Leo,

It doesn't work for me either. There seem to be a number of things
that
do
not work in the BeforeClose event. Try this instead -

Sub auto_close()
Application.StatusBar = "Bye"
' or
' Application.StatusBar = False
End Sub

Regards,
Peter T

"LEO@KCC" wrote in message
...
Hi,

Could someone please explain the following behaviour?

The Workbook_BeforeClose event is triggered after the
ThisWorkbook.Close
method is called and all lines in the Event sub are executed but the
status
bar never actually changes its text, and no exceptions are thrown
either.

Add this to the ThisWorkbook module and test:

Sub RunThisMacro()
Application.StatusBar = Rnd
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = "THIS DOES NOT WORK" 'this is executed but
it
doesn't actually do anything.
End Sub

Thanks for your insight.

Leo

I don't think the problem is specifically something that somehow "locks" the
statusbar. As I mentioned before this is not the only thing that 'doesn't
work' in the close event. Eg, while trying to find a workaround the
Application.OnTime method also fails.

Could you confirm this suggestion worked for you (as it did for me) -

ThisWorkbook.RunAutoMacros xlAutoClose ' called in the BeforeClose event
and in the Auto_Close routine change the statusbar

Regards,
Peter T


"LEOT" wrote in message
...
Peter, some good ideas there. Thanks.

I think that there may be something that "locks" the statusbar after
calling
the Close method. Very wierd.

Leo T

--
Regards,

Leo T
"Peter T" <peter_t@discussions wrote in message
...
Obviously simplest would be to include code to 'do' the statusbar just
before calling code to close the wb, though I assume that's not viable.

But almost as simple would be to place the code to 'do' the statusbar in
the
workbook deactivate event, possibly only called subject to a module
level
boolean flag that's set in the Close event.

Peter T

The code does

"Peter T" <peter_t@discussions wrote in message
...
Hmmm, you're right, I forgot about that.

Things I tried in the BeforeClose event -

1.
Call another macro to run the code to clear the statusbar. Result the
macro
is called OK but the statusbar code doesn't work

2.
if len(application.statusbar) then
Cancel = true
Application.OnTime Now, "ReClose"
end if

' in a normal module
Sub ReClose()
Application.StatusBar = False
ThisWorkbook.Close
end sub

Result - the ontime macro is not called at all, also the wb does not
close
due to Cancel = true

3. This works for me, not sure why

again in the BeforeClose event -

ThisWorkbook.RunAutoMacros xlAutoClose

Sub auto_close() as posted previously, ie code to set or clear the
statusbar.

Indeed strange, but as I say the above appears to work for me.

Regards,
Peter T




"LEO@KCC" wrote in message
...
Hi Peter, The Auto_Close sub is not executed when the Close method of
ThisWorkbook is called (this is in the Help documentation), so I
still
have
the problem...

Regards,

Leo T


"Peter T" <peter_t@discussions wrote in message
...
Hi Leo,

It doesn't work for me either. There seem to be a number of things
that
do
not work in the BeforeClose event. Try this instead -

Sub auto_close()
Application.StatusBar = "Bye"
' or
' Application.StatusBar = False
End Sub

Regards,
Peter T

"LEO@KCC" wrote in message
...
Hi,

Could someone please explain the following behaviour?

The Workbook_BeforeClose event is triggered after the
ThisWorkbook.Close
method is called and all lines in the Event sub are executed but
the
status
bar never actually changes its text, and no exceptions are thrown
either.

Add this to the ThisWorkbook module and test:

Sub RunThisMacro()
Application.StatusBar = Rnd
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = "THIS DOES NOT WORK" 'this is executed but
it
doesn't actually do anything.
End Sub

Thanks for your insight.

Leo