Excel Macros - Show Progress Bar in Excel While Running Macro

[ashish]

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

Download Working Macro  https://www.box.com/s/0a6b971ebda2d944017f

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

 Download Working Example  https://www.box.com/s/75768cb330466a7b2481