How to Create a Custom Progress Bar in Excel with VBA

The primary ingredients to making this work are:

• VBA
• UserForms
• Loops

The complete code and the downloadable file can be found at the end of this post.  The following sections provide high-level explanations of each major facet of code.  For a more in-depth explanation of each facet, consider enrolling in the complete “Unlock Excel VBA & Excel Macros” course at XelPlus.

Part 1: The Task(s) to be Automated

What we are automating can be anything our Excel minds can imagine.  The great thing about this progress bar tutorial is that it can be inserted into almost any existing or future macro.

In this demonstration, our automation process will “walk” through records in a table, pausing for 1/10^th of a second at every record.

In reality, we would likely be performing much more sophisticated operations on each record.

Part 2: The Visual Interface

The scroll bar window is created using a VBA UserForm.

1. In the Visual Basic Editor, insert the UserForm (Insert – Userform).

This presents us with a blank, generic UserForm which acts as a canvas for our imagination’s unlimited creativity.

NOTE: If you are missing the Toolbox controls, select View -> Toolbox.

2. Rename the UserForm by selecting (Name) in the Properties We will name our UserForm “UserForm_v1”.

3. Set the UserForm Caption (what is displayed in the Title Bar area) to read “Create PDF Documentation”. This, of course, can be anything you wish it to display.

4. Using the ToolBox Label feature, insert a label and set the Caption to read “Your Assistant at Work…”

5. Using the Toolbox Image feature, insert an image placeholder. Set the Picture property to the location of your image.  The ellipse button to the right of the property will allow you to browse out to your image file.

6. Using the Toolbox Frame feature, insert a frame object. This will serve as a border for the scroll bar as well as display a numeric percent counter.  Set the Caption to read “0%”.  This will be changed to read a progressive percentage during code execution.

7. Within the boundary of the Frame, insert another Label This label will not contain text, rather it will act as the scroll bar.  This is performed by coloring the interior of the label and resizing it incrementally, making it larger and larger as the macro progresses.  Label Properties you may wish to experiment with are:

• BackColor – &H00C00000& (Blue)
• BackStyle – 1-fmBackStyleOpaque
• BorderColor – &H80000006& (Gray)
• Height – 30
• SpecialEffect – 1-fmSpecialEffectRaised
• Width – 18

Featured Course

Unlock Excel VBA & Excel Macros

Automate ANYTHING you need done in Excel with VBA and macros. Go from Beginner to VBA Expert and design automations with confidence.

Learn More

Part 3: The Code That Drives the Visual

Double-clicking on any blank area of the UserForm will open the code window for the UserForm.

Some of the major features of the code are:

Variable Declarations

Turning off screen updating (to reduce screen flicker) and suppressing alerts (if encountered.)

Check to ensure there is at least one record in the table being processed

Looping through the table rows

A few notes about the above snip of code:

• The loop will execute ‘N’ number of times as there are rows in the table.
• The PCT = calculates an ever-increasing percentage. This begins at 1/N and ends with N/N.  (i.e. 1% to 100%).  The more records in the table, the more granular the percentage calculation.
• The timer will count to 1/10^th of a second, creating a very small pause effect. This keeps the progress bar from moving too fast for this demonstration.  In the real world, you would want to omit this self-induced “pause” as it hampers performance.
• The Call UpdateProgress(Pct) line passes the calculated percentage (Pct) to the UpdateProgress This percentage will be displayed in the Caption of the Frame object.

Removing the form from the screen when finished

Part 4: Launching the UserForm

To display the UserForm when the user clicks the macro launch button, we create a traditional Module sheet (Insert -> Module) and load the UserForm into memory.

To ensure the UserForm for the progress bar appears in the center of the screen, we calculate the center position with some crafty math.  After the calculations are complete, we Show the memory-loaded UserForm.

Part 5: Announcing Code Completion

You can inform the user that the code has completed in a myriad of ways.  Our code will display a Message Box that informs the user to retrieve their report from the printer.

A forced line break was added to the code for easier reading.

Part 6: Cleanup

The screen updating and error reporting are re-enabled.

Part 7: Making the Scroll Bar “Stretch”

This is where the magic happens.

Remember that part earlier where we made a call to another macro named “UpdateProgress”?  We passed that macro a value stored in a variable named Pct.

The value in Pct serves two purposes:

• The value of Pct is displayed in the Caption of the Frame
• Pct is used to calculate the Width property of the label object.
• The .Repaint instruction forces the label object to be visually refreshed based on the newly calculated Width

By redrawing the label object at an ever-increasing larger width, we achieve the illusion that the label object is growing.  Ingenious, no?

The “DoEvents” instruction allows VBA to detect user-interaction via the keyboard.  This is helpful during long-duration macros where the user may wish to break out of a loop prematurely.

Part 8: Assigning the Macro to the Launch Button

You can launch a macro variety of objects: buttons, images, icons, shapes, etc.

We will add an Excel icon image and assign the macro to the image.  This is done by right-clicking on the image and selecting “Assign Macro”.

Next, in the Assign Macro dialog box, select the macro located on the traditional module sheet that loads and shows the UserForm.  In our case, it was named “GetMyForm_v1”.

Part 9: Testing the Progress Bar Macro

Click the launch icon to test your amazing creation.

And there you have it, a homemade progress bar.

This second version of the progress bar was designed by my friend Bryon Smedley. I love it! It’s really creative.

It takes the opposite approach to displaying progress.  Instead of the Label “grow”, we will have the Label “shrink”.

The trick here is that our Label will not be the indicator of progress.  Instead, we have a static image indicating progress and the Label will act as a mask that hides part of the static graphic.

By coloring the Label the same as the background and placing the Label in front of the Image, we can reveal portions of the Image as we reduce the size of the Label.

When we “shrink” the Label it will give us the illusion of “growing” the Image.

Code Differences

For the most part, the code is the same.  The main differences lie in the scroll bar/mask and the percentage display.

Percentage Display

Instead of inserting a Frame object and changing the Caption property, we will add a Text object and change the Caption property.

The gray background is an inserted Image object that pointed to a simple image of a gray, bordered shape.

Progress Bar (Static Image)

The green “Excel” progress bar is a static image of a green rectangle with the Excel logo repeated four times.

Progress Bar (“Shrinking” Mask)

The Label object that “shrinks” has two main differences in operation compared to the first example.

1. The Width property is calculated by multiplying Pct by 218 (the maximum width) and deducting that from 218. Ex: if Pct is .5, then the Width is 109; half of the original 218.
2. Instead of fixing the Left property to a set position, the left side of the Label will be calculated. The logic is to deduct the calculated Width from 230 (the far-right side of the Label).  Ex: if Pct is .5, the calculated Width is 109, then the Left property is calculated at 121.

NOTE: These results represent the number of pixels.  109 represents the number of pixels wide. 121 represents 121 pixels from the left edge of the UserForm.  Depending on your UserForm size, you may need to experiment with these values.  A bit of experimentation may be required to achieve the perfect look.

Be Careful of the Stacking Order (Layers)

Make certain that the Label object is in front of the Image object.  This can be accomplished by right-clicking on an object and selecting either “Bring Forward” or “Send Backward”.

Macro Assignment & Testing

After you have assigned the “GetMyForm_v2” macro to the launch button, we can see the magic that is the “shrinking” progress bar.

Download Workbook

Feel free to Download the Workbook HERE.

Full Code for Version 1

Procedure “GetMyForm_v1”

Sub GetMyForm_v1()
Load UserForm_v1
With UserForm_v1
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With
    
End Sub

Procedure “UserForm Activate” (v1)

Private Sub UserForm_Activate()
Dim startrow As Integer
Dim endrow As Integer
Dim i As Integer
Dim myScrollTest As Object
Set mainbook = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set myScrollTest = Worksheets("ScrollTest_v1")
mylabel = Worksheets("ScrollTest_v1").Range("A2").Value
With myScrollTest
    'where to start
    startrow = .Range("A1").Row + 1
    
    'where to end
    endrow = .Range("A1").End(xlDown).Row
    
    If .Range("A2").Value = "" Then
        MsgBox "Please paste your entity codes starting from Row 2"
        Exit Sub
    End If
    
End With
'start the loop
For i = startrow To endrow
Pct = (i - startrow + 1) / (endrow - startrow + 1)
    
Call UpdateProgress(Pct)
    
' This is where your workbook does many things that take a bit of time
    startTime = Timer ' Capture the current time
    Do
    Loop Until Timer - startTime >= 0.1 ' Advance after 1/10th of a second
    
' This is where your workbook has finished an itteration of work
Next i
Unload UserForm_v1
myScrollTest.Select
MsgBox "Report generation is complete" & vbLf & vbLf _
    & "Please retrieve your report from the printer", vbInformation
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Procedure “UpdateProgress” v1

Sub UpdateProgress(Pct)
With UserForm_v1
    .FrameProgress.Caption = Format(Pct, "0%")
    .LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
        
    .Repaint
    
End With
DoEvents
End Sub

Full Code for Version 2

Procedure “GetMyForm_v2”

Sub GetMyForm_v2()
Load UserForm_v2
With UserForm_v2
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With
End Sub

Procedure “UserForm Activate” (v2)

Private Sub UserForm_Activate()
Dim startrow As Integer
Dim endrow As Integer
Dim i As Integer
Dim myScrollTest As Object
Set mainbook = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set myScrollTest = Worksheets("ScrollTest_v2")
mylabel = Worksheets("ScrollTest_v2").Range("A2").Value
With myScrollTest
    'where to start
    startrow = .Range("A1").Row + 1
    
    'where to end
    endrow = .Range("A1").End(xlDown).Row
    
    If .Range("A2").Value = "" Then
        MsgBox "Please paste your entity codes starting from Row 2"
        Exit Sub
    End If
    
End With
'start the loop
For i = startrow To endrow
Pct = (i - startrow + 1) / (endrow - startrow + 1)
Call UpdateProgress(Pct)
    
' This is where your workbook does many things that take a bit of time
    startTime = Timer ' Capture the current time
    Do
    Loop Until Timer - startTime >= 0.1 ' Advance after 1/10th of a second
    
' This is where your workbook has finished an itteration of work
Next i
    
Unload UserForm_v2
myScrollTest.Select
MsgBox "Report generation is complete" & vbLf & vbLf & "Please retrieve your report from the printer", vbInformation
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Procedure “UpdateProgress” v2

Sub UpdateProgress(Pct)
With UserForm_v2
    .Complete.Caption = Format(Pct, "0%") ' Percentage displayed to user as numeric
    
    .LabelProgress.Width = 218 - Pct * 218 ' Shortens the mask
    .LabelProgress.Left = 218 - .LabelProgress.Width + 12 ' Repositions the mask
    .Repaint
End With
DoEvents
End Sub

LinkedIn Twitter YouTube

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

More About Leila Join 400,000+ professionals in our courses