Vba Progress bar help !!

wahmed

Board Regular
Joined
Jun 28, 2010
Messages
66
Hello,

Can someone help me to understand how to use Excel Vba Progress bar Control ? I have already added that control to my VBA Tool box through Tool box Additional Controls...

If you can make it easy for my understanding with simple examples then that would be great help.

Thanking you.

Wahmed
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG02Aug01
[COLOR="Navy"]Dim[/COLOR] Pausetime
[COLOR="Navy"]Dim[/COLOR] Start
[COLOR="Navy"]Dim[/COLOR] olet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Pausetime = 0.1
  
  [COLOR="Navy"]With[/COLOR] ProgressBar1
   .Visible = True
   .max = 100
   [COLOR="Navy"]For[/COLOR] olet = 1 To 100
        DoEvents
        Start = Timer
            [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] Timer < Start + Pausetime
            .Value = olet
            [COLOR="Navy"]Loop[/COLOR]
            
        [COLOR="Navy"]Next[/COLOR] olet
.Visible = False
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

wahmed

Board Regular
Joined
Jun 28, 2010
Messages
66
Dear MickG,

Thanks for your reply.

Can you let me know where should I add these Commands in Progress bar control or in Form initialize ?

Waiting

Wahmed
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, If you are running this in a "Userform" then set the "Progress Bar in the form with a Command Button on the form to run the code.
Double Click the Command Button , with the Userform in Design Mode, and insert the code.

NB:- If you run this directly from the Command Button remove the reference to "Sub MG02Aug01"
NB:- When you have it working I imagine you will incororate it into some code That the Progress Bar relates to Rather than the Basic "oLet" Loop.
NB:- If you Right click the ProgresBar in design Mode and choose properties, then Under the Sub Heading "Custom" you can alter the Scolling method from "Standard" to Smooth, I think it looks better, What ever suits you.
Regards Mick
 

wahmed

Board Regular
Joined
Jun 28, 2010
Messages
66
Dear MickG.

Thanks for quick reply , Yes, This time it worked Great, thanks for your help, but if you can guide me more then I shall be thankful.

What is Olet and how it work ? so that I can use if further for more different progress bars as well.

How Can I add percentage with this progress bar ?

Thanking you.

Wahmed
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

Hi Have a look at Application status bar in the Vb Help, or below

<TABLE style="WIDTH: 229pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=305 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 229pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=305 colSpan=4 height=17>http://www.j-walk.com/ss/excel/tips/tip34.htm

I thing Progress Bars really need to be Used in relation to the Code you trying to Run.

With the progress Bar I showed the Timer is really used to Replicate your Code running and the loop "olet" represents your loop within your code..
Regards Mick
</TD></TR></TBODY></TABLE>
 
Last edited:

wahmed

Board Regular
Joined
Jun 28, 2010
Messages
66
Dear MickG,

Thanks for your help.

luckily I have managed to have very easy and understandable commands for new users of Excel vba for progress bar which is mentioned below...


Sub progressbar()

Dim f As UserForm1
Dim i As Integer

Set f = New UserForm1

f.Show modeless

f.ProgressBar1.Min = 0
f.ProgressBar1.Max = 10000

For i = 1 To 10000
f.ProgressBar1.Value = f.ProgressBar1.Value + 1
DoEvents
Next

f.Hide
Set f = Nothing

End Sub

This macro can be use with a userfrom and a progress bar on it.

But now my problem is that I need some simple commands for adding percentage meter with that progress bar , so can you please help me about that or someone else please ??

Thanking you.

Wahmed
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, I don't think you can Add text to that progress Bar, If you want to represent the percentage completed then you could add a"TextBox to the Userform and alter the code as Below.
Code:
For i = 1 To 10000
f.ProgressBar1.Value = f.ProgressBar1.Value + 1
f.TextBox1 = Format(i / 1000 / 10, "0.00%")
DoEvents
Next
Regards Mick
 

wahmed

Board Regular
Joined
Jun 28, 2010
Messages
66
Dear MickG.,

thanks for your reply.

I already have found command by adding label box which shows percentage ...

Sub PROGRESS()

Dim f As UserForm1
Dim i As Integer


Set f = New UserForm1

f.Show vbModeless

f.ProgressBar1.Min = 0
f.ProgressBar1.Max = 10000

For i = 1 To 10000
f.ProgressBar1.Value = f.ProgressBar1.Value + 1
DoEvents
f.Label1.Caption = Int(f.ProgressBar1.Value * 100 / f.ProgressBar1.Max) & " % "
Next

Unload f
Set f = Nothing

Exit Sub
End Sub

So now if you can help me to how to change color for progress bar ?? sorry for asking too many questions :)

regards

Wahmed
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Progress bars do not seem to have a colour property, that probably why people use other controls, and modify for a progress bar.
Regards Mick
 

Forum statistics

Threads
1,171,861
Messages
5,877,946
Members
433,301
Latest member
MJ_Mac

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top