Excel VBA: Create a Progress Bar While Macro Is Running

In this article, I’ll show you how you can create a progress bar to check the progress of your Macro while it’s running using VBA in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

6 Quick Steps to Create a Progress Bar While a Macro Is Running in Excel VBA

A progress bar is required when we have a long code to run in our hands. Designing progress bars in VBA is easy. It’s will be better if you have a little familiarity with VBA UserForms. But don’t worry. It’s okay if you are going to work with UserForms here the first time. Follow the below-mentioned steps and you’ll end up developing a beautiful UserForm for checking the progress of your Macro.

Step 1: Open the Visual Basic Window

• Press ALT + F11 on your keyboard to open the Visual Basic Window.

Step 2: Insert a New UserForm

• After you have opened a new Visual Basic window, go to the Insert > UserForm option in the toolbar to insert a new UserForm. Click on UserForm.
• A new UserForm called UserForm1 along with a Control box will open. This UserForm will act as our progress bar.

Step 3: Drag Tools to the UserForm

• Now we have to drag tools to this UserForm. We’ll need only 3 tools for our purpose. One Frame, and two Labels.
• The first Label (Label1) will lie horizontally along the top margin of the UserForm, covering its whole width.
• The Frame (Frame1) will lie horizontally in the middle of the UserForm, also covering the whole width.
• Finally, the second Label (Label2) will cover the whole area of the Frame.

Step 4: Put Code to the UserForm

• Now we have to insert code into the UserForm. Right-click on the UserForm from the left pane, and select View Code.

• You have to enter your main VBA code here. That is, enter the task you want to perform here.

I want to enter 5 in each cell of the first 100 rows and columns of the worksheet. So my code will contain 2 for-loops stretching from 1 to 100.

Here you have another important task. To measure the progress of your code, you have to divide the whole code into 100 sections. Then change the width of the second Label (Label 2) according to the completion of the code.

• Therefore, I change the width of Label 2 according to the first for-loop.

And one more thing. You have to keep the whole code within a Userform_Activate private subprocedure. It’ll be executed as soon as the UserForm is loaded.

Therefore the complete VBA code will be:

⧭ VBA Code:

Private Sub UserForm_Activate()

For i = 1 To 100
    For j = 1 To 100
        Cells(i, j) = 5
    Next j
    UserForm1.Label1.Caption = Str(i) + "% Completed"
    UserForm1.Label2.Width = i * 2
Next i

End Sub

Step 5: Create a Macro to Run the UserForm

• Finally, we’ll create a Macro to run the UserForm. Go to Insert > Module in the Visual Basic toolbar to insert a new module.

Then insert the following VBA code there.

⧭ VBA Code:

Sub Progress_Bar()
UserForm1.Caption = "Progress Bar"
UserForm1.Label1.Caption = "0% Completed"
UserForm1.Label2.Caption = ""
UserForm1.Label2.BackColor = vbHighlight
UserForm1.Frame1.Caption = ""
Load UserForm1
UserForm1.Show
End Sub

Step 6: The Final Output

• If you have completed the above steps successfully, you have created your own progress bar. Now to check the output, run the Macro called Progress Bar.

• All the cells within the first 100 rows and columns will take 5. And a progress bar will run showing the progress of your task.

Things to Remember

• Here I’ve used the BackColor property of a Label object to measure the progress. But you can use any relevant property that may suit your need.

Conclusion

So, this is the process to develop a progress bar to measure the progress while a Macro is running using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.