Pages

Wednesday, August 15, 2012

Show Progress Bar in Excel While Macro is Running

If you want to display the progress bar while running the macro. Snapshot below -




Step 1 

Press  Alt+ F11 


Step 2

Right click on  "This workbook" -> Insert -> Userform



Step 3

When you will click on insert user form . you will see a new form inserted  and "Tool box " next to it. 
In case you are not able to see the tool box  - goto "View" menu and click on"TOOLBOX"
Right click on  Tool box and click on Additional control


Step 4
Choose Microsoft Progress Bar Control and click on ok.  



Step 5
You will see a new control "Progress bar" . Select it and drag it over the form . 


Step 6 
Adjust the size of form and progress bar.


Here is the code- 
Sub show_progress_bar()
Dim i As Integer
With Prog_bar
    'SET MIN value to 0
    .ProgressBar1.Min = 0
    'SET Max value as per your requirement
    .ProgressBar1.Max = 10000
    .Show vbModeless
    '.ScrollBars = fmScrollBarsVertical
    ' run a loop to display progres bar
    For i = 1 To 10000
        'change the value of progress bar to show the progress using fill color
        .ProgressBar1.Value = i
        ' chnage the caption of user form to display the percentage of task completed
        .Caption = VBA.Format(i / Prog_bar.ProgressBar1.Max, "0%") & "  Complete"
        DoEvents ' DoEvents allows the UserForm to update.
    Next
End With
' task finish unload progress bar
Unload Prog_bar
End Sub

Example 1  I have tried to use the progress bar in clean and trim macro . I have taken a range "a1:a10000" and  now i will clean and trim the range using vba and use progress bar to display the progress of task.
Here is the code

    Sub example_1()
    Dim rng As Range, i As Long, clnrng As Range
    
    Set clnrng = Range("a1:a10000")
    i = 0
        With Prog_bar
            'SET MIN value to 0
            .ProgressBar1.Min = 0
             'SET Max value equal to cells count
            .ProgressBar1.Max = clnrng.Cells.Count
            .Show vbModeless
        End With
        For Each rng In clnrng.Cells
            ' do clean and trim using vba
            rng.Value = Application.WorksheetFunction.Clean(Application.WorksheetFunction.Trim(rng.Value))
            i = i + 1
            'change the value of progress bar to show the progress using fill color
            Prog_bar.ProgressBar1.Value = i
            ' chnage the caption of user form to display the percentage of task completed
            Prog_bar.Caption = VBA.Format(i / Prog_bar.ProgressBar1.Max, "0%") & "  Complete"
            DoEvents ' DoEvents allows the UserForm to update.
        Next
    ' task finish unload progress bar
    Unload Prog_bar
End Sub


2 comments:

  1. When I try to create the progress bar I get an error message "Library not registered".

    ReplyDelete
  2. This progress bar is not working in my system i am using Excel 2013 64 bits please tell me

    ReplyDelete