Help on Making A Progress Bar.

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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
1589203634446.png


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


Userform1 Active After Loops Have Completed...........................
1589203940249.png
.........
1589204163386.png


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.
1589204427772.png

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
VBA Code:
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
I think I may have done it
1589207411788.png


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


If I change the Vba to this,
VBA Code:
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
VBA Code:
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.
1589207755160.png


Loops more than intended.

1589207675136.png


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

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Have a look here and see if it helps?

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

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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

1593079629799.png
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
So you want
VBA Code:
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:
VBA Code:
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:
VBA Code:
  With Sheets("Sheet10")
    Bar.Width = ProgressBarWidth(.Range("Q2"), .Range("Q1"), .Range("Q4"))
    Bar.Value =  FormatPercent(.Range("Q3"))
  End With
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Welcome the function I provided will keep from the bar ever being longer than the max length :cool: Are you all sorted out now?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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