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.
How to Create a Progress Bar While a Macro Is Running in Excel VBA (With Easy Steps)
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
UserForm1.Repaint 'Updating Screen
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
“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.Label1.Width = i * 2
Next i
End Sub”
In code you writing “UserForm1.Label1.Width = i * 2”, but picture below “UserForm1.Label2.Width = i * 2”.
The width should change for Label2. Get attention on this.
P.S. thx for information
Dear Geogre,
Thanks for your kind attention and suggestion. We updated it.
My macro takes a long time to execute, that is why I want to show the progress, but for some reason the bar reaches the 100% before the code has finished. How can I make sure that the 2 things are related?
Dear Eugenia,
Thank you for your comment.
To ensure that the progress bar accurately reflects the progress of your macro, you can use the following modified code.
• Insert this code into your module.
Here, the Progress_Bar subroutine is called by the cmd_Click event. We used the (Counter1 / 1000) * 100 formula to calculate the progress of the macro execution as a percentage and set it as the value of StatusBas.
• Then, create a button in your worksheet >> right-click on it >> select Assign Macro.
• Assign the Progress_Bar named macro to that button.
Now, if you click on the button, It will show progress in the status bar.
We hope this will solve your problem. Please let us know if you face any further problems.
Regards,
Arin Islam,
ExcelDemy
this is helpful.
But I don’t see repaint in the code, in my file, without this .repaint command, the bar is not shown properly.
Dear Yuan,
Thanks for the suggestions. The code is updated you can check it now.
Regards
ExcelDemy