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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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

ADVERTISEMENT

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

ADVERTISEMENT

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,640
Messages
5,512,566
Members
408,904
Latest member
Buttnoid02

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top