progress bar - danielep71/VBA-PERFORMANCE GitHub Wiki

Home

Below we will look at a program in Excel VBA that creates a progress indicator. We've kept the progress indicator as simple as possible, yet it looks professional.

To create this Userform, execute the following steps.

1. Open the Visual Basic Editor

2. Click Insert, Userform. The Userform only consists of three controls:

   • a frame control
   • and two label controls

3. Add the frame control. Properties:

   • empty the Caption field
   • set the Height to 24 and the Width to 204.

4. Add the first label control and place it in the Frame control:

   • Change the name to "Bar"
   • BackColor to Highlight
   • empty the Caption field
   • set the Height to 20 and the Width to 10

5. Add the second label control and place it above the Frame control:

   • change the name to Text
   • change the Caption to '0% Completed'

6. Change the caption of the Userform to Progress Indicator. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier.

7. Add the following code line:

    Private Sub UserForm_Activate()
        Code
    End Sub
   

8. Place procedures into a standard module

    Sub Code()
        Dim i As Integer, j As Integer, pctCompl As Single
        Sheet1.Cells.Clear
        For i = 1 To 100
            For j = 1 To 1000
                Cells(i, 1).Value = j
            Next j
        pctCompl = i
        Progress pctCompl
        Next i
    End Sub
   
    Sub Progress(pctCompl As Single)
        UserForm1.Text.Caption = pctCompl & "% Completed"
        UserForm1.Bar.Width = pctCompl * 2
        DoEvents
    End Sub
   

9. Show UserForm1

When you use this technique, the macro takes just a little longer to complete its intended tasks.

Home