How to create Progress bar in Excel using UserForm VBA

Its a basic progress bar that determines the progress of a process in VBA. You can use it in any way you want.

Follow these steps.

1) In the VBA editor, open Project Explorer window, right click the project to insert a UserForm.

2) Next, add a Label control from the toolbox.

3) Right click UserForm and select Properties

We'll have to change the default properties of the UserForm and Label as per our requirement.

UserForm Properties

* In the "properties" window, clear the caption of the userform. We'll keep the caption of the form empty and it will be changed dynamically (at runtime) using a code (macro).

* Set the Width property to 200.

Label Properties

Now, let's set the label properties. Right click the label and choose "Properties".

* Remove or clear the caption.

* Set a minimum value for the label. We'll assign the width at runtime using a macro.

The design is ready. We'll write the macro the display the progress bar.

Double click Userform or press F7 to open the code window. Add the below code in userform.

Option Explicit

Private Sub UserForm_Activate()
    showProgress
End Sub

Sub showProgress()
    Application.ScreenUpdating = False
    UserForm1.Label1.Caption = ""     Clear the caption.

    Dim iCounter, iRow, iCol As Integer
    iCounter = 1
    iRow = 6
    iCol = 1
    
    Dim iMax As Integer
    iMax = 2000
    
    For iCounter = 1 To iMax
        DoEvents
        
        ' Enter values in the sheet.
        Application.Worksheets("sheet1").Cells(iRow, iCol) = iCounter
        
        ' Update Label width and UserForm caption.
        UserForm1.Label1.Width = Round((iCounter / iMax) * 100, "0")
        UserForm1.Caption = "Loading. Please wait... " & Round((iCounter / iMax) * 100, "0") & "%"
        iRow = iRow + 1
        
        If (iRow = 15) Then
            iCol = iCol + 1
            iRow = 6
        End If
    Next iCounter
    
    Me.Hide     ' Hide UserForm when process completes.
End Sub

You can open the userform or should I say, the progress bar from anywhere you want. I'll open the progress bar from a worksheet with click of a button. Therefore, I'll write this in the button's click event.

Private Sub cmd_Click()
        UserForm1.Show     ' Open userform to show the progress bar.
End Sub

The width of Label is dynamically changed inside the for loop. It creates a progress bar effect.

The UserForm caption also shows the progress in % (percent). See the image in the beginning.

← PreviousNext →