0

i have created a macro and i am trying to add a progress bar to it. The only thing i've done so far is to create a UserForm where i have 4 Labels: White line label = Label1; Green line label: Label2; In progress label: Label3; the label under in progress: Label4. And here is an image:

enter image description here

And my code has the following function: I take the values from the Range M:O, insert them in B4, B7 and B9 and for every single entry for B4, B7 and B9 i create a PDF document. Example: 100 entries for B4, B7 and B9 = 100 Pdf Documents. I want to create a progress bar which displays the whole progress. Any ideas how? The only thing i managed to create is to display a message in the status bar and to make the cursor still..

Code:

Sub SavePDF4()
    Const sPath = "C:\Users\TestMacro\"
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Set ws1 = ThisWorkbook.Worksheets("Protocol")
    Set ws2 = ThisWorkbook.Worksheets("Input")
    m = ws2.Range("M:O").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.Cursor = xlWait
    Application.DisplayStatusBar = True
    Application.StatusBar = "Please Wait.."
    For r = 1 To m
        ws2.Range("J5").Value = Range("J5").Value + 1
        ws1.Range("B4").Value = ws2.Range("M" & r).Value
        ws1.Range("B7").Value = ws2.Range("N" & r).Value
        ws1.Range("B9").Value = ws2.Range("O" & r).Value
        ws1.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=sPath & "Report_" & ws2.Range("J5").Value & ".pdf"
    Next r
    Application.Cursor = xlDefault
    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Angel Velkov
  • 13
  • 2
  • Does this answer your question? [Progress bar in VBA Excel](https://stackoverflow.com/questions/5181164/progress-bar-in-vba-excel) – braX Mar 02 '20 at 10:08
  • You can create a progress bar in a user form by nesting a label within the frame of another label. Both labels are blank but the outside label has a white background while the inside one has a solid background color. Then you adjust the width (for a horizontal bar) of the inside label in relation to the percentage of progress. Readjust after each so-many loops. At its fullest expansion the width will reflect 100% of progress. You may consider displaying the percentage as a number as well. – Variatus Mar 02 '20 at 10:08
  • Or [THIS](https://stackoverflow.com/questions/10782394/pop-up-the-excel-statusbar) for some fancy progressbars – Siddharth Rout Mar 02 '20 at 10:19

0 Answers0