?
Solved

Progress  bar in excel vba

Posted on 2005-04-21
13
Medium Priority
?
2,000 Views
Last Modified: 2010-08-16
I want to add a progress bar to the macro in excel vba. This is how my macro works

For i=1 to 15
call step1
call step2
<------------------------the progress bar should get updated at this point.
next

or may I should
load the scree for progress bar and run my loop
Please help..
0
Comment
Question by:Aiysha
  • 6
  • 5
  • 2
13 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 2000 total points
ID: 13837034
Hi Aiysha,

Whenever I need to put a progress bar in excel, I use the idea found here on John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip34.htm

Although I don't really use them anymore, as even the minor slow down of these is too slow for me.  But if you are interested and have any questions, I'd be happy to help you walk through it if you need it.

If you don't want to have the userform for the progress bar, you can use the status bar in the bottom left of excel for progress, using similar idea as see on the progress bar above (having a percent compete listing).  
Another way is to use the "Control the LED Display in the StatusBar" idea, the 6th option on http://j-walk.com/ss/excel/files/developer.htm

As I said, I use the userform method when I really need a progress bar or if I am just going to make a few updates.  But repeatedly calling it to change the progress takes too much time, in my opinion.

Matt
0
 

Author Comment

by:Aiysha
ID: 13837292
Sub ShowDialog()
    UserForm1.LabelProgress.Width = 0    <------------------"cannot find the specified object" I get error here please help
    UserForm1.Show
End Sub
0
 
LVL 35

Expert Comment

by:mvidas
ID: 13837372
It sounds like it could be one of two things with that error.
-Is your userform called "UserForm1" ?
-Did you name the label control on the userform "LabelProgress"?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Aiysha
ID: 13837511
yes I did both its correct
0
 
LVL 35

Expert Comment

by:mvidas
ID: 13837646
Hmm, it just worked for me again when I just build another from scratch..
I thought you might have been running this from a different workbook than the progress userform exists in, but you'd get an "object required" error instead if that were the case (at least in xl2000)

OK, this assumes you haven't deselected the "Auto List Members" option in Tools/Options from the VBE.
Inside that module you have your code, type    userform1    and when you press the   .    after that, the list of that user form's members appears.  If it doesn't, again verify the (Name) of the userform is still UserForm1.
As soon as you type "la" of labelprogress after the  .  then LabelProgress should be highlighted.  If not, again verify that the label control on userform1 has a (Name) of LabelProgress.
Once you press the   .   after LabelProgress, when you hit the "w" of width, it should highlight Width for you.  If not, verify that the LabelProgress is indeed a label (When you select the labelprogress control on the userform, you should see "LabelProgress  Label" in the dropdown box of the properties window).

If you've done all these things, try creating the userform again following all the steps on that page.  If you're still having trouble I can post a sample workbook that contains a working example of this so you can make sure that it will work on your computer
0
 

Author Comment

by:Aiysha
ID: 13837693
the module code is alright.. I think its the userform1_activate() function thats not working.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 13837759
Try changing it to  userform_activate  for the title, that should cause the event to work
0
 

Author Comment

by:Aiysha
ID: 13838614
it works now..thanx for all the help.. but my question now is what will happen if the user closes the progress form.. is there a way to disable the close option on the progress form..
Thank you.
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13842070
Yes by doing something like so :

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Cancel = 1
End Sub

If you want a message box or some other way of checking to see if you want the form to be closed you would obviously add that into this event ie use an if statement or a select case to determine if it has finished doing what you want it to do before you do the Cancel = 1
0
 

Author Comment

by:Aiysha
ID: 13843110
Thank for all the help..I will close the question soon..
mvidas, I am having difficulty in making the progress bar loop

My problem is simple

for i= 1 to total_runs
PctDone =(100*i/)15
.FrameProgress.Caption = Round(PctDone, 2) & "%"
             .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10) -----> I dont know how to program this to reflect

next i




0
 
LVL 35

Expert Comment

by:mvidas
ID: 13843500
It looks like gecko answered your question about closing the userform, so I won't even bother going into that

As for the progress indicator, I'm a little confused by your use of 15, but I think what you're looking for might be something like:

    For i = 1 To total_runs
        PctDone = 100 * i / total_runs
        With UserForm1
             .FrameProgress.Caption = Round(PctDone, 2) & "%"
             .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
        DoEvents
    Next i

If not, can you explain what you're trying to do differently?
Matt
0
 
LVL 35

Expert Comment

by:mvidas
ID: 13843958
My apologies, I forgot that you multiplied the PctDone by 100 to get the rounding in there.

You could either change the .LabelProgress.Width line to
             .LabelProgress.Width = PctDone / 100 * (.FrameProgress.Width - 10)

Or you could just use

    For i = 1 To total_runs
        PctDone = i / total_runs
        With UserForm1
             .FrameProgress.Caption = Format(PctDone, "0.00%")
             .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
        DoEvents
    Next i

(changed the round to 0.00% and removed the *100 and /100)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
The viewer will learn how to implement Singleton Design Pattern in Java.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

809 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