So a few days ago I published an article about using conditional formatting to create a progress bar in an Excel Worksheet to display, in real-time, the progress of some process to your users.  You can read all the details at:

That said, I then pushed a little further to create a reusable progress bar utilizing a Userform that could be incorporated within any Microsoft Office application and that works as a pop-up overlay to display the progress of a process.

The Solution

By creating a progress bar via a userform, we can now easily export/import the objects into any Microsoft Application :

• Access
• Excel
• Outlook
• PowerPoint
• Outlook
• …

and we can customize it to include:

• Captions
• Messages
• Change the color(s)
• Change the font(s) (color, size, family, …)
• Anything else we want!

resulting in things like:

Helper Function

I have chosen to also use one helper function simply to center the useform.  This is completely optional! Do note this does not work in Access, Outlook.

Sub CenterUserform(oUF As Object)
    With oUF
        .Top = (Application.Height / 2) - (.Height / 2)
        .Left = (Application.Width / 2) - (.Width / 2)
    End With
End Sub

Usage Example

In the Demo I have a simple GenericTest sub which illustrates the basic usage, which would look a little like:

Sub GenericTest()
    'Generic Testing
    Dim i                     As Long
    Const lNoIteration As Long = 73

    ProgressBar.Show
   
    For i = 1 To lNoIteration
        ProgressBar.ProgressBar_Progress (i / lNoIteration)
    Next i
End Sub

Or adding a few options to customize it a little:

Sub GenericTest()
    'Generic Testing
    Dim i                     As Long
    Const lNoIteration As Long = 73

    ProgressBar.Show
    Call CenterUserform(ProgressBar) 'Only works in Excel, PowerPoint, Word
    ProgressBar.ProgressBar_ProgressOverlay True    'Place the % value on top of the progress bar
    ProgressBar.ProgressBar_Resize 150, 325         'Resize the userform (smaller in this case)
    ProgressBar.ProgressBar_Color RGB(253, 109, 13) 'Change progress bar color - Orange
    ProgressBar.ProgressBar_Caption "Progress Bar Simulation" 'Add a form caption
    ProgressBar.ProgressBar_Message1 "Here's a demonstration of an Excel Userform Progress Bar." & vbCrLf & vbCrLf & "Enjoy!"
    ProgressBar.ProgressBar_Message2_Align fmTextAlignCenter 'Center align the Message2 content

    For i = 1 To lNoIteration
        ProgressBar.ProgressBar_Message2 "Iteration " & Chr(10) & i
        ProgressBar.ProgressBar_Progress (i / lNoIteration)
        Sleep 25
    Next i
    
    ProgressBar.ProgressBar_Message2 "Operation Completed Successfully!"
End Sub

Correction!

I stated erroneously, in my YouTube video, that Microsoft Access didn’t support the Userform Progress Bar, but Carsten Gromberg was kind enough to correct me on this in the comments below. I incorrectly assumed, that since the userform option did not appear in Microsoft Accesss VBE menu or toolbar as an option

that it wasn’t in fact supported.

As Carsten pointed out, this is false. Userforms are fully supported. So we can import the same userform in Access and use it in the same manner. As he mentions, by importing the userform, Access will automatically add the necessary ‘Microsoft Forms 2.0 Object Library’ reference to the VBA project.

Also note that you can customize the VBE and drag the Userform command into the menu and/or toolbar so it is available.

A great big Thank you to Carsten for teaching this old dog a new trick!

Download The Demo File

Feel free to download a 100% unlocked copy (includes an Access, Excel and Word Demo Files) by using the link provided below: