Excel: Statusbar control

[565u]

Hi and thanks for reading my post!

When you display a message in the status bar in Word (StatusBar = "Your message."), the message is shown and as soon as you do something, Word takes control of the status bar and displays whatever information it has to display. I like this approach and I would like to use it in Excel. Unfortunately it does not seem to work just that way.

In Excel, you can display your message (Application.StatusBar = "Your message.") but that message occupies the status bar until you return control to Excel (Application.StatusBar = False). I would probably need some global code that would count down some time after the message has been displayed and then return control to Excel. I have no idea how to achieve this or how to solve this any other way :-( Could anyone kindly help me a bit here, please?

Thanks in advance!

Best regards,

Pavel

 

[SkipVought]

Hi,

Have you looked at VBA Help on StatusBar Property?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[565u]

Hi,
thanks for replying, but are you serious? I am really stuck here.


PH

 

[SkipVought]

I am serious as a heart attack!

There is an example in HELP that addresses this very issue.

Did you ever look?



Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Have a look at the Application.OnTime method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NWBeaver]

I believe Skip is referring to the example code, which I have pasted in a routine below:

Sub statusbar_message()

oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Please be patient..."
Workbooks.Open Filename:="LARGE.XLS"
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar

End Sub

You just need to replace one line with your code to do something like run a timing loop or go to sleep.
It's really not hard, just give it a little thought.

 

[565u]

SkipVought and NWBeaver,
thanks for taking your time to reply, but if you look closely at my original post, I am using the very lines from the VB Help to make my point. I probably should have explained one thing a bit more clearly - I need to exit the procedure that displays the message in the statusbar. Therefore any loop that would return the control of the statusbar to excel is not a solution.
Best,
PH

 

[565u]

PHV,
I have no idea what it is. Therefore it sounds both tempting and promising Thanks for your tip!!
Best,
PH

 

[SkipVought]

Sub five_second_statusbar_message()
Dim oldStatusBar, p As Date, t As Date, m As String

oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
m = "The countdown has started. Please be patient..."
p = 5 / 24 / 60 / 60
t = Now
Do
    Application.StatusBar = m & Format(p - (Now - t), "ss")
    DoEvents
Loop Until Now > t + p
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar

End Sub

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[565u]

Thanks SkipVought, but as I'd mentioned before I need to leave the code that displays the statusbar message behind, therefore any loop is not an option.
Still thanks anyway!
Best,
PH

 

[565u]

Thank you, PHV!
Tried it. It works!! I had no idea such beautiful function existed. It solves my problem perfectly and it even solves some other stuff I had been wrestling with.
Thanks tons!!!
PH