How to show a progress bar in Excel using VBA - Two simple methods

1st Method – Using the Status bar

There is a built-in status bar at the bottom left corner of your worksheet.

If you have a macro that runs a long loop, you can embed a small piece of code in between the loop, which can show a message, like process in progress or simply show % of process completed etc. in the status bar.

Here’s an example.

Option Explicit

Private Sub cmd_Click()
    showProgress
End Sub

Sub showProgress()
    Dim iCounter, iRow, iCol As Integer
    iCounter = 1
    iRow = 6
    iCol = 1

    For iCounter = 1 To 2000
        DoEvents
        Cells(iRow, iCol) = iCounter

        Application.StatusBar = "Loading. Please wait... " & Round((iCounter / 2000) * 100, "0") & "%"
        iRow = iRow + 1

        If (iRow = 15) Then
            iCol = iCol + 1
            iRow = 6
        End If
    Next iCounter

    Application.StatusBar = "Data loaded " & Round((iCounter / 2000) * 100, "0") & "%"
End Sub

The above process is very simple. I am just running a for loop 2000 times to test the macro.

While the macro is running the loop, a piece of code updates the status bar. Look here…

Application.StatusBar = "Loading. Please wait... " & Round((iCounter / 2000) * 100, "0") & "%"

The built-in StatusBar property is often used to show a message. We can use this to show the progress of a process.

There’s another important thing that you should know, is that I am using DoEvents function inside the for loop. It actually helps to show the progress simultaneously. See the above image.

However, “there is a slight problem”. The status bar is located at the bottom left corner of the worksheet. Its hardly visible and can easily be missed.

Therefore, a dialog showing a progress bar at the middle of the worksheet will be ideal in this case. It will be right in front of you and you can see the progress.

2nd Method – Using a UserForm

In the 2nd method, we’ll actually create our own progress bar, using a UserForm.

The progress bar, in this example, is a pop-up window (or a dialog box), which will be displayed at the center of the worksheet.

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

Open the Toolbox and add a Label control. The Label is our progress bar. I’ll show you how.

After you have added the Label control, right click the UserForm and select Properties.

We need to change the default properties of the UserForm and Label that you have inserted.

UserForm Properties

1) In the Properties window, clear the Caption of the UserForm. We’ll update the caption from the code (macro).

2) Set the Width property to "200" (approx). You can change the width according your choice.

Label Properties

Now, this is important.

Right click the Label control (on the UserForm) and select Properties.

1) Remove or clear the caption

2) Reduce the Width of the Label. The width will be assigned at runtime through the macro. "That's the trick".

That’s it. The design part is ready. Now lets check the code.

Double click the UserForm or Press F7 to open View Code window.

Add this code in the UserForm.

Option Explicit

Private Sub UserForm_Activate()
    ShowProgress
End Sub

Sub ShowProgress()
    Application.ScreenUpdating = False
    UserForm1.Label1.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

Open the UserForm from the worksheet.

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

Look carefully, the macro for the Progress is almost same as the code I have shown in the 1st method. However, there are few changes.

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

UserForm1.Label1.Width = Round((iCounter / iMax) * 100, "0")

This is how we show the progress of the process animatedly as a dialog or a popup window.

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

Run the macro. If everything is done correctly, you’ll see this output.

Happy coding. 🙂

← PreviousNext →