Aiysha
asked on
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
<------------------------t he 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..
For i=1 to 15
call step1
call step2
<------------------------t
next
or may I should
load the scree for progress bar and run my loop
Please help..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"?
-Is your userform called "UserForm1" ?
-Did you name the label control on the userform "LabelProgress"?
ASKER
yes I did both its correct
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
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
ASKER
the module code is alright.. I think its the userform1_activate() function thats not working.
Try changing it to userform_activate for the title, that should cause the event to work
ASKER
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.
Thank you.
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
Private Sub UserForm_QueryClose(Cancel
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
ASKER
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
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
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
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
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)
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)
ASKER
UserForm1.LabelProgress.Wi
UserForm1.Show
End Sub