Application.StatusBar

[VegasNath]

I ran a macro earlier that contained the following:

Application.StatusBar = "This is a message."

Where is the benefit of using the status bar for displaying a message? (I cannot think of one), and at what point does it "go away"?

[HansV]

You can use it to keep the user informed of the progress of your macro in an unobtrusive way.

For example, I have an Excel "application" with a macro that performs a large number of validity checks for each row of a worksheet. If the sheet has many rows, this may take a while, so I use code like this:

Code: Select all

m = ... ' last used row
For r = 2 To m
  Application.StatusBar = "Checking row " & r " & " of " & m
  ' Validity checks
  ...
Next r
Application.StatusBar = ""

The text will remain displayed until you explicitly change or clear it, or until you do something that causes Excel to display something in the status bar.

[VegasNath]

Thankyou Hans, your explanation makes perfect sense, and I can see the benefit of using code in that way. That will be very useful.

[VegasNath]

Hans,

I have found this concept to be extremely useful, Thankyou very much!

One issue that I have is that when I use:

Application.StatusBar = ""

at the end of the process, my normal status bar messages (like saving progress bar) disappear for a while, and there is no telling when normality may return.

Any thoughts?

[HansV]

Try using

Application.StatusBar = False

instead

[VegasNath]

That did the trick.

:Cheers:

[VegasNath]

.......Back again

I am finding the concept of using the statusbar to relay messages extremely useful in long running sections of code!

I use:
Application.statusbar = "" to relay a message
&
Application.statusbar = False - to reset the statusbar back to normality, which generally works well.

I am having a small issue whereby I am resetting the s/b to false immediately prior to workbook save commands, where I want to see the saving progress bar. However, this does not show.

Would this be a timing issue? or..... ? Any suggested workarounds?

[HansV]

Try inserting a line

DoEvents

after setting StatusBar to False.

[VegasNath]

That does not work.

Code: Select all

Application.StatusBar = "xyz"

..... more code

    Application.StatusBar = False
    DoEvents
    ActiveWorkbook.SaveAs Filename:=strPath4 & "xyz" & Format(strDate, "dd.mmm.yy") & ".xls"
    ActiveWindow.Close

Any other idea's? Thanks for looking.

[HansV]

In that case, I don't think you can do anything about it.

[Rudi]

That does not work.
Any other idea's? Thanks for looking.

VegasNath,

Are you refering to the save progress as in the attached picture.

If this is the case, I created code that alters the statusbar, then reset it to False (without DoEvents), and it showed the status of saving the file? Let me know if I am misunderstanding your query??

[VegasNath]

Hi Rudi,

Yes, it is the save progress bar that I want to show, but it is not showing even after resetting the s/b to false, prior to the save command.

Neither Doevents or Application.wait makes a difference.

[Rudi]

Yes, it is the save progress bar that I want to show, but it is not showing even after resetting the s/b to false, prior to the save command.

The only thing I can think of is that your file is being saved so quickly that the progress bar does not get time to show. In my example (where I made that screen print), I created a file that was 10000 rows in length and it took about 5 seconds to save. The code is no different to yours where I simply reset the S/B. Maybe your file is too small and the save process is too quick.

[ChrisGreaves]

Application.StatusBar = "This is a message."

Try also:

Code: Select all

Application.Caption = "This is a message."

[VegasNath]

No worries, this is annoying, but it's not a show stopper. Thanks all.