CODEWITHSUNDEEP
vbaexcel
z32a7ul
z32a7ul

Reputation: 3777

Excel VBA Application.StatusBar in Worksheet_Deactivate fails with 50290

I have a Worksheet, which updates the StatusBar based on which cell is selected (this works fine). My problem is, with the code that sets the StatusBar back to empty when the user goes to another Worksheet:

Private Sub Worksheet_Deactivate()
    Application.StatusBar = vbNullString ' Run time error here
End Sub

Err.Description is: "Method 'StatusBar' of object '_Application' failed", Err.Number is: 50290.

This error occurs only if the user changes from Worksheet to Worksheet rapidly (by pressing Ctrl+PgUp or Ctrl+PgDown) and does not happen in case of switching to another Sheet slowly.

Why do I have this error?

Upvotes: 0

Views: 1115

Answers (2)

z32a7ul
z32a7ul

Reputation: 3777

I found the problem. When an event handler starts execution, the Excel Application may not be ready, so this has to be checked if the code refers to objects related to the Application:

Private Sub Worksheet_Activate()
    If Application.Ready = False Then Exit Sub
    ' Rest of the code referring to Application.x or Me.y or ActiveSheet.z, etc.
End Sub

Upvotes: 0

Bernd Stoeckel
Bernd Stoeckel

Reputation: 145

Just set it to False

Application.StatusBar = False

from Microsoft:

This property returns False if Microsoft Excel has control of the status bar. To restore the default status bar text, set the property to False; this works even if the status bar is hidden.

Upvotes: 1

Related Questions