Status Bar Hangover

[ExcelMonkey]

I have a routine that I have written that still has some
bugs in it. When it crashes, my status bar stays frozen
in the bottom left of the screen. I know to clear the
bar I would use:

Application.StatusBar = ""

There must be a way using error handling that says On
Error Application.StatusBar = "". Does anyone know how
exactly to do this. And where exactly can I put in my
code to trigger in case of any error whatsoever?

Thank-you.

 

[Dick Kusleika]

EM

Here's one way to structure your subs

Sub Test()

On Error Goto Err_Hndlr

'Code here

CleanUp:
Application.StatusBar = ""
Application.ScreenUpdating = True
Other stuff to clean up
Exit Sub

Err_Hndlr:
MsgBox Err.Number & Err.Description
Resume CleanUp

End Sub

 

[ExcelMonkey]

Bob, I already have a line of code at the beginning of my
routine that says:

On Error Resume Next

How does your code dovetail with this? Do I have to
remove my Resume Next?

 

[Dick Kusleika]

I'll tell you, but only if you stop calling me Bob.

Yes, the On Error Goto Err_Hndlr replaced On Error Resume Next.. With
Resume Next, you're saying continue on to the next line if there's an error,
essentially ignoring the error. With my construct, you report the error, do
some clean up stuff, and exit out of the sub.

There may be times when you need an On Error Resume Next in the middle of
your sub. You can still use it, but don't use an On Error Goto 0 when
you're done, use Goto Err_Hndlr.

On Error Resume Next
Set wsh = Worksheets("Data")
On Error Goto Err_Hndlr

If wsh Is Nothing Then
etc...