CODEWITHSUNDEEP
vbaexcel
Citanaf
Citanaf

Reputation: 464

Excel VBA - Call macro to clear status bar after a few seconds

I am trying to figure out if there is a way to call a macro to clear the status bar after the execution of a different macro. I realize that I can simply use:

Application.Wait(Now + TimeValue("00:00:05"))
Application.StatusBar = False

However, what I want to do is have my original macro end by saying something as:

Application.StatusBar = "Macro Function Complete."
Call clearStatusBar
End Sub

Sub clearStatusBar()
'I do not want the application.wait here because it locks up the excel program. 
Application.Wait(Now + TimeValue("00:00:05"))
Application.StatusBar = False
End Sub

Is there a way to delay the "Application.StatusBar = False" while still allowing the user to access the program?

Let me know if you need more clarity. Thanks for your help.

Upvotes: 3

Views: 11777

Answers (2)

Citanaf
Citanaf

Reputation: 464

Sorceri was able to provide me with the correct method for my request. By using the Application.OnTime method, you can call your macro on a delay without tying up the program.

Application.StatusBar = "Macro Function Complete."
Application.OnTime Now + TimeValue("00:00:07"), "clearStatusBar"
End Sub

Sub clearStatusBar()
Application.StatusBar = False
End Sub

Upvotes: 2

user6432984
user6432984

Reputation:

enter image description here

Sub TestClearStatusBar()
    Application.StatusBar = "Testing: ClearStatusBar"
    ClearStatusBar
End Sub

Sub ClearStatusBar(Optional ClearStatusBar As Boolean)
    If ClearStatusBar Then
        Application.StatusBar = False
        MsgBox "Cleared"
    Else
        Application.OnTime Now + TimeValue("00:00:05"), "'ClearStatusBar True'"
    End If
End Sub

Upvotes: 7

Related Questions