Progress bar in excel vba

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..
AiyshaAsked:
Who is Participating?
 
mvidasCommented:
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
 
AiyshaAuthor Commented:
Sub ShowDialog()
    UserForm1.LabelProgress.Width = 0    <------------------"cannot find the specified object" I get error here please help
    UserForm1.Show
End Sub
0
 
mvidasCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AiyshaAuthor Commented:
yes I did both its correct
0
 
mvidasCommented:
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
 
AiyshaAuthor Commented:
the module code is alright.. I think its the userform1_activate() function thats not working.
0
 
mvidasCommented:
Try changing it to  userform_activate  for the title, that should cause the event to work
0
 
AiyshaAuthor Commented:
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
 
Shane Russell2nd Line Desktop SupportCommented:
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
 
AiyshaAuthor Commented:
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
 
mvidasCommented:
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
 
mvidasCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.