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.

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.

Opening VBA Window to Create a Progress Bar While a Macro is Running in Excel VBA

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.

Inserting a New UserForm to Create a Progress Bar While a Macro is Running in Excel VBA

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.

Opening UserForm Code to Create a Progress Bar While a Macro is Running in Excel VBA

  • 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

VBA Code for Displaying Progress Bar in an UserForm

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
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.

Progress Bar While a Macro is Running in Excel VBA

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.


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.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!


Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

  1. “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

  2. 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.

      Private Sub cmd_Click()
      End Sub
      Sub Progress_Bar()
      	Dim Counter1, Row_no, Col_no As Integer
      	Counter1 = 1
      	Row_no = 1
      	Col_no = 1
      	For Counter1 = 1 To 1000
          	Cells(Row_no, Col_no) = 5
          	Application.StatusBar = "Loading data... " & Round((Counter1 / 1000) * 100, "0") & "%"
          	Row_no = Row_no + 1
          	If (Row_no = 5) Then
              	Col_no = Col_no + 1
              	Row_no = 1
          	End If
      	Next Counter1
      	Application.StatusBar = "Done " & Round((Counter1 / 1000) * 100, "0") & "%"
      End Sub

      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.
      Arin Islam,

  3. 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.


Leave a reply

Advanced Excel Exercises with Solutions PDF