Solved

How do i display progress meter  on Excel statusbar?

Posted on 2000-03-02
22
1,101 Views
Last Modified: 2012-05-07
How do i display progress  meter on Excel statusbar?
0
Comment
Question by:mdanny
  • 5
  • 4
  • 3
  • +6
22 Comments
 
LVL 9

Expert Comment

by:antrat
ID: 2575916
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
0
 

Expert Comment

by:qsupporter
ID: 2575979
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/ 
0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2575981
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
0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2576055
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

0
 
LVL 9

Expert Comment

by:antrat
ID: 2576074
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
 
0
 
LVL 1

Author Comment

by:mdanny
ID: 2576542
This I know already.I thought there's may be API call...
0
 
LVL 22

Expert Comment

by:ture
ID: 2577281
mdanny,

Please delete your old duplicate question.

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

/Ture
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2578502
Antrat & TigerMan (& other interested)

look at this question:
http://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
0
 
LVL 13

Expert Comment

by:cri
ID: 2580388
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
0
 

Expert Comment

by:ShoeBooty
ID: 2581035
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

0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2610808
mdanny,

Just a friendly reminder.

Upgrading to answer.

Dave
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:mdanny
ID: 2611424
This is not an answer to my question...
0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2611759
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


0
 
LVL 1

Accepted Solution

by:
mdanny earned 0 total points
ID: 2612985
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2802695
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
0
 
LVL 3

Expert Comment

by:darinw
ID: 2909402
Community Support has reduced points from 50 to 0
0
 
LVL 3

Expert Comment

by:darinw
ID: 2909403
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
0
 
LVL 3

Expert Comment

by:darinw
ID: 2909406
Comment accepted as answer
0
 
LVL 13

Expert Comment

by:cri
ID: 2938369
darinw, thanks for helping to clean up the list I posted in CS. Are you a employEE or a voluntEEr ?
0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2938697
Cri,

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

Dave
0
 
LVL 13

Expert Comment

by:cri
ID: 2942588
ActAAlly A Am AftAr pEEnts...
0
 
LVL 3

Expert Comment

by:darinw
ID: 2945940
I am an employEE.

darinw
Customer Service
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now