Help on Making A Progress Bar.

[Sharid]

I have looked a t several progress bar, on the web but I CAN NOT get any of them to work with my code, (My Post Mr Excel). So I decided to create a basic one which works on Sheet change events. It kind of works but I need a bit more help. Appreciate if anyone could help.

Userform1 IN DESIGN MODE. I have places a label and called it "Bar", this is the image

UserForm1 Active, Nothing is Showing as 0% in sheet10 Q3.....Sheet 10

.....

Userform1 Active After Loops Have Completed...........................

.........

If I multiple by 10. All though 100% is reached the bar is not at 100%. I think the issue is the max width of the bar has not been determined so though the loops equal 100% the bar does not.

Code that I am using
I tried to MULTIPLE the figure by 10 in Sheet10, Q3 to increase the bar size. kind of worked. But Still not right. The Bar changes depending on the % in Q3

Private Sub Worksheet_Change(ByVal Target As Range)
UserForm1.Bar.Width = Sheets("Sheet10").Range("Q3")  ' * 10 '
End Sub

Please could some one help.

Thanks in advance.

 

[Sharid]

I think I may have done it

I have just added this, 250 is the width of the Label called "bar"

If I change the Vba to this,

Private Sub Worksheet_Change(ByVal Target As Range)
UserForm1.Bar.Width = Sheets("Sheet10").Range("Q2")  * Sheets("Sheet10").Range("Q5")
End Sub

Now it is multiplying the progress by what ever Q5 will be. That is Loops divided by Length of bar in above example that is 100/250 = 2.50

Or may be this

Private Sub Worksheet_Change(ByVal Target As Range)
UserForm1.Bar.Width = Sheets("Sheet10").Range("Q2")  * 250 ' LENGTH OF LABEL CALLED "Bar"
End Sub

If anyone knows a better way please advise. As I have noticed that if there is an error and IF more loops are done that expected the bar size increase, so it is not fully fix.

Loops more than intended.

The only work around I can see if to place the label "bar" in a frame for now, it will still increase but will not exceed the frame hopefully

 

[CSmith]

Have a look here and see if it helps?

Also for a percentage try: Int(100 * Part / WholeOrMax)

 

[Sharid]

Hi

I have got my progress bar working. However I am not 100% sure on how to display the % in the Textbox, could someone please explain what CSmith is advising on " Int(100 * Part / WholeOrMax) " the percentage is on Sheet10 cell G7 and is showing as 87.50% . I am using the textbox control source to get the data from the sheet to the textbox. How do I display it in the % format in the textbox

 

[CSmith]

The link I provided has examples and a form you can import to see progress bar code. Sending this via cellphone. Will attempt more detailed information once I get to PC.

 

[Sharid]

Thanks for your reply,

I have seen and tried the progress bar that you recommended a few months back. I could not get it to work with my code. So I made a much simpler one which works for me. It counts the number of Loops of the code and works out the lenght of the progress, see above images, this bit is fine. The spreedsheet also works out the % completed.

The only bit I am Now stuck on is displaying THAT % from the sheet to the TextBox. The % is in Sheet10 Cell G7 and the TextBox is called PercentageTextBox. As stated above I am using the textbox control source to get the data from the sheet to the textbox. However I can not display it in the % format in the textbox, See above above image.

 

[Sharid]

I found this Mr Excel NOT 100% sure how to tweek this for my problem

 

[CSmith]

So you want

Bar.Value =  FormatPercent( Range("Q3"))

as you said Bar was the label control's name.

Also, try this function for any max bar length desired:

Function ProgressBarWidth(currentPortion, maxPortionsOrWholeAmt, barTotalLength As Integer) As Single
  If (1 < (currentPortion / maxPortionsOrWholeAmt)) Then
    ProgressBarWidth = barTotalLength
  Else
    ProgressBarWidth = barTotalLength * currentPortion / maxPortionsOrWholeAmt
  End If
End Function

You can use like this:

  With Sheets("Sheet10")
    Bar.Width = ProgressBarWidth(.Range("Q2"), .Range("Q1"), .Range("Q4"))
    Bar.Value =  FormatPercent(.Range("Q3"))
  End With

 

[Sharid]

thanks

 

[CSmith]

Welcome the function I provided will keep from the bar ever being longer than the max length Are you all sorted out now?