Vba Progress bar help !!

[wahmed]

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

 

[MickG]

Hi, Try this:-

[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]

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]

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]

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]

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>

 

[wahmed]

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]

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.

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]

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]

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