How do i display progress meter on Excel statusbar?

Hi

I don't think you can display the Progress meter but you can display a message while your macro is running like below

Sub StatBar()
Application.DisplayStatusBar = True
Application.StatusBar = "Hello"
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Application.StatusBar = ""
End Sub

Or you can place a Progress meter on a user form.


antrat

Hi Antrat,

The status bar of Excel already has an inbuilt progress bar. You can not change, enable/disable the progress bar.

If the status bar itself is not visible (at the bottom) in your Excel, click "View" menu & click "Status Bar".

Hope this answers your problem.

Bye,
QSupporter
help@qsupport.net

http://www.qsupport.com/ 

mdanny,

I don't think it is possible to place an object in the status bar.

As a primitive substitute for the glitz of a progress bar, you could code some text messages:

   Application.StatusBar = "10%"
   DoMore
   Application.StatusBar = "20%"
   DoMore
   etc , etc
   
Or place the update message inside a processing loop.  The following code establishes a loop to run 200 times.  Each pass through the loop an evaluation is performed and the StatusBar is updated in 10% increments.

Sub DoProcessing()

EndOfLoop = 200

For Counter = 1 To EndOfLoop

   Percent = Format(CStr(Application.WorksheetFunction.Round(Counter / EndOfLoop, 2)), "0.00")

   If Right(Percent, 1) = 0 Then
      Application.StatusBar = Right(Percent, 2) & "%"
   End If

   DoSomthingHere
   
Next Counter

End Sub

It can easily be modified to report different percentage breaks.

Hope that is of at least some help.

Dave

gsupporter,

The usual protocol in this site is to provide comments.  When you propose answers, you lock everyone else out.

IMHO both antrat and myself have provided superior answers that provide the functionality that mdanny is seeking.

Why don't we leave mdanny to evaluate multiple answers and select the one most suited?

Dave

I have to agree TigerMan, gsupporter. The normal proticol on EE is to only post as an answer if you are quite sure you have one. Once the question is placed in the locked section it will not get as much attention from others experts :)

antrat
 

This I know already.I thought there's may be API call...

mdanny,

Please delete your old duplicate question.

https://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10302242 

/Ture

Antrat & TigerMan (& other interested)

look at this question:
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10303911 

I think the same comment I provided applies here, so I'll post it again:

##################################################
>Geesh Qsupporter, that really is an answer !

Have you actually read the question ?

Now I see from your profile you're new at this site. A few explanations might be good. Please read the question carefully, and the following comments. Please take no offense, but everyone can make mistakes and surely everyone is enthousiastic the first time they enter this site and see the possibility to earn points by answering questions. For that purpose, you can use the 'comments' instead of using answers. In fact, we only use the 4answer' option here if we are 100 % sure it IS the answer, AND if no one else has proposed the same or more in a previous COMMENT. It is not because I did not push the 'Answer' button, that I don't know the answer. Of course, we have to wait for the opinion of the questioner (Chrism), but I'm pretty confident my comment above will do the job for him.

Please don't use the answer button anymore in the future, as it only upsets people as you might have felt from my comment. You have an option to withdraw your own answer. I kindly invite you to do that in this case. Another reason for not using the answer button is that the question gets in the 'Locked Questions' area, which is not visited by a lot of Experts, as they think the question is solved and just waits for approbation by the questioner. When it's not the case, the problem persist and  the question doesn't get the attention it would deserve.

Finally, you're very wellcome in the ExpertsExchange community, and I hope we'll meet again in better circumstances.

##################################################

Apologies for the intervention, mdanny, and of course I'm listening the discussion with interest......

(I may interfere later on, this question
might help to get over Antrat :-)

Calacuccia

Statusbar is inside a dialog (i.e. not the requested statusbar), but have a look:
http://www.j-walk.com/ss/excel/tips/tip34.htm

I use a percentage in the status bar to indicate to users where the process currently is.  For instance if I have a worksheet with 1000 lines of data in it and I want to evaluate each line, I place this in a loop, such as:

Sub evaluatelines()

cells(2,1).select

lr = selection.end(xldown).row

For r = 2 To lr
    Application.StatusBar = "Evaluating Rows...(" & Format(r / lr, "0.00%") & " complete)"

'whatever code is needed on each row

next r

Application.StatusBar = False

End Sub

This doesn't provide a graphical status bar, but does enable you to provide the same information.

Shoe

mdanny,

Just a friendly reminder.

Upgrading to answer.

Dave

This is not an answer to my question...

First, antrat said:

I don't think you can display the Progress meter but you can display a message while your macro is running like below

Sub StatBar()
Application.DisplayStatusBar = True
Application.StatusBar = "Hello"
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Application.StatusBar = "" 
End Sub

Or you can place a Progress meter on a user form.


And then TigerMan said:


I don't think it is possible to place an object in the status bar.

As a primitive substitute for the glitz of a progress bar, you could code some text messages:

   Application.StatusBar = "10%"
   DoMore
   Application.StatusBar = "20%"
   DoMore
   etc , etc
   
Or place the update message inside a processing loop.  The following code establishes a loop to run 200 times.  Each pass through the loop an evaluation is performed and the StatusBar is updated in 10% increments.

Sub DoProcessing()

EndOfLoop = 200

For Counter = 1 To EndOfLoop

   Percent = Format(CStr(Application.WorksheetFunction.Round(Counter / EndOfLoop, 2)), "0.00")

   If Right(Percent, 1) = 0 Then
      Application.StatusBar = Right(Percent, 2) & "%"
   End If

   DoSomthingHere
   
Next Counter

End Sub

It can easily be modified to report different percentage breaks.

Hope that is of at least some help.


These are both correct in so far as they have answered your question: You cannot put a progress bar in the Status Bar.  Not only did we inform you of that situation - an undeniable reality - but we both went to some trouble to provide you with alternatives.

I would consider that an answer.

For what it's worth I can point you to a 'simulated' type of progress bar - one that is quite resource intensive and will slow down your processing no end.  However, that would seem to drinking a coolie without taking the top off.

If you wish such a beast, then you could reply, otherwise you might like to think about closing the question by evaluating the responses.

Dave

mdanny,

I know you made separate questions for several experts to get their points.

Unless you expect more input, I would suggest to either delete this question, or ask to Community Support (http://www1.experts-exchange.com/Customer_Service/Experts_Exchange/) to grade a comment of your own (so that this interesting question gets stored in the PAQ Database) and get your points refunded.

Calacuccia

Community Support has reduced points from 50 to 0

Hello everyone.

I am refunding the points on this question, reducing the points to zero and moving the thread to the PAQ.

-- I am accepting one of mdanny's comments as an answer --

darinw
Customer Service

Comment accepted as answer

darinw, thanks for helping to clean up the list I posted in CS. Are you a employEE or a voluntEEr ?

Cri,

It is good to sEE some humour amongst our pEErs and other mEEk advisors.  Do have a dEEp and meaningful wEEk.

Dave

ActAAlly A Am AftAr pEEnts...

I am an employEE.

darinw
Customer Service