Application.StatusBar not refreshing after ThisWorkbook.Close

  • Thread starter Thread starter LEO@KCC
  • Start date Start date
L

LEO@KCC

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
 
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
 
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
 
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, 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
 
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
 
Back
Top