A Quick Excel VBA Progressbar

To create an Excel VBA progressbar...

1. Open a new Workbook.

2. Press ALT + F11 to open the VBA Editor.

3. Create a UserForm - right click on anything in the VBProject pane at the top left and click on Insert > UserForm.

4. Change the caption of the UserForm to <i>Please wait...</i>.

5. Create a label covering the whole form...

Excel VBA Progressbar

6. Double click on the label, delete the code, and add this code...
<pre>
Private Sub UserForm_Activate()
    ' Set the width of the progress bar to 0.
    frmProgressBar.LabelProgress.Width = 0
    Call Main
End Sub
</pre>
7. Create a new Module - right click on anything in the VBProject pane at the top left and click on Insert > Module.

8. Add this code...

Sub Excel_VBA_ProgressBar()
    frmProgressBar.Show
End Sub

Sub Main()
    Dim pctCnt As Integer
    Dim cnt As Integer
    Dim Completed As Single
    Application.ScreenUpdating = False
    pctCnt = 1
    For cnt = 1 To 100
        For a = 1 To 500000
        Next
        pctCnt = pctCnt + 1
        Completed = pctCnt / 100
        frmProgressBar.LabelProgress.Width = Completed * frmProgressBar.Width
        DoEvents
    Next
    Unload frmProgressBar
End Sub

9. Place the cursor in any one of the three lines of the VBA Progressbar Sub to run it.

Return from Excel VBA Progressbar to VBA Code Samples

Return to our Homepage