Excel VBA UserForm Progress Bar: Build One That Works

To add an Excel VBA UserForm progress bar, create a UserForm with a Frame and a colored Label inside it, then update the Label’s width as your macro completes units of work. Show the form modelessly and call DoEvents occasionally so Excel can repaint it; Excel has no built-in UserForm progress-bar control.

What a UserForm progress bar does

A progress bar gives a long-running macro a visible indication of how far it has progressed. A typical design has a track, a colored fill that grows from left to right, and a text label such as “Processing record 250 of 1,000.” The percentage is calculated from completed work divided by total work. It is a visual aid, not a measurement of elapsed time.

In Excel for Microsoft 365 on Windows, the straightforward approach uses standard Microsoft Forms controls: a UserForm, a Frame, and one Label placed inside the Frame. The Frame acts as the track, while the Label’s width represents progress. This method avoids external libraries and ActiveX progress-bar controls, which can create compatibility and registration problems.

A bar is most useful when the macro has a known number of steps, such as looping through a set number of rows or files. If the task’s duration varies greatly between steps, the bar can still show completed steps, but it should not imply that the remaining time is predictable.

Plan the form and its controls

Open the Visual Basic Editor with Alt+F11 on Windows. In the editor, choose Insert > UserForm. In the Properties window, set the form’s (Name) property to frmProgress and give its Caption a useful title, such as “Working.” Add these controls from the Toolbox:

A Frame named fraTrack, used as the empty progress track.
A Label inside the Frame named lblFill, used as the colored portion of the bar.
A Label on the form named lblStatus, used for a short status message.
Optionally, a Label named lblPercent, used to show a percentage.

Place the fill Label inside the Frame, not merely over it on the form. Set its Caption to blank, choose a visible BackColor, and set BorderStyle to None if you want a simple solid fill. Give it a small starting width. Position it near the Frame’s left edge; the code below assumes a small border allowance in its width calculation. Set the status label’s Caption to an initial message, and make the form large enough that the text is readable.

Code the progress update method

Double-click the UserForm and add this procedure to its code module. It clamps values to the valid range, so an accidental negative completion count or a count beyond the total will not make the bar extend outside its track. If the total is zero or negative, it displays an empty bar rather than dividing by zero.

Option Explicit

Public Sub UpdateProgress(ByVal completed As Long, _
                          ByVal total As Long, _
                          ByVal message As String)
    Dim fraction As Double

    ' Avoid division by zero, then keep progress between 0 and 100 percent.
    If total <= 0 Then
        fraction = 0
    Else
        fraction = completed / total
    End If

    If fraction < 0 Then fraction = 0
    If fraction > 1 Then fraction = 1

    ' The 8-point allowance depends on the Frame's border and layout.
    Me.lblFill.Width = (Me.fraTrack.Width - 8) * fraction
    Me.lblStatus.Caption = message
    Me.lblPercent.Caption = Format$(fraction, "0%")
End Sub

If you do not add the optional percentage Label, remove the lblPercent assignment. Likewise, change the control names in the code if you used different names. The width allowance is a layout detail, not a universal constant: adjust it if the fill overruns or leaves too much empty space at 100 percent.

Show the form and report progress

Put the calling macro in a standard module. This example uses a loop of 1,000 units and refreshes the display every ten units. The comment marks where actual work belongs; replace it with the operation your macro needs to perform. The sample includes cleanup so the form closes and Excel’s prior ScreenUpdating setting is restored if the procedure succeeds or encounters an error.

Option Explicit

Sub RunWithProgress()
    Dim frm As frmProgress
    Dim i As Long
    Dim total As Long
    Dim oldScreenUpdating As Boolean

    On Error GoTo CleanFail

    total = 1000
    oldScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False

    Set frm = New frmProgress
    frm.Show vbModeless

    For i = 1 To total
        ' Replace this comment with one unit of your real work.

        If i Mod 10 = 0 Or i = total Then
            frm.UpdateProgress i, total, _
                "Processing record " & i & " of " & total
            DoEvents
        End If
    Next i

CleanExit:
    On Error Resume Next
    If Not frm Is Nothing Then Unload frm
    Application.ScreenUpdating = oldScreenUpdating
    On Error GoTo 0
    Exit Sub

CleanFail:
    MsgBox "The macro stopped: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z, and the example’s placeholder does not perform useful work until you replace it. In the Visual Basic Editor, run the procedure with the cursor inside it and press F5, or return to Excel and run it from the Macro dialog. Macros must be enabled for the workbook and its source to be trusted.

Why modeless display and DoEvents matter

frm.Show vbModeless lets the macro continue while the form remains visible. A normal modal form would pause the calling procedure until the user closes the form, which is not the usual behavior wanted for a progress display. Modeless display alone does not guarantee smooth visual updates: VBA runs on Excel’s main UI thread, so a long stretch of work without yielding can prevent the form from repainting.

DoEvents yields control briefly so Windows and Excel can process pending events, including painting the form. Calling it on every tiny operation can add overhead, so the example updates every ten loop iterations. Choose an interval appropriate to the work: a few times per second is usually easier to follow than hundreds of rapid redraws. A loop that updates a worksheet cell by cell may need a different performance strategy as well; the progress display does not make inefficient worksheet operations faster.

There is an important tradeoff: while VBA yields, users may interact with Excel or the modeless form. If your macro cannot safely tolerate interaction, consider disabling relevant controls or providing a Cancel button with deliberate cancellation logic. Do not assume that showing a bar makes the macro run in the background; ordinary VBA code still occupies Excel’s main thread.

Make the percentage meaningful

The simplest calculation treats every unit as equal: after 250 of 1,000 rows, the bar reads 25 percent. That is honest only if the units have reasonably similar cost. If the first 900 steps take a second and the final 100 take a minute, a row-based bar can appear nearly complete while most of the time remains.

For work with different phases, use a weighted total or report phase-specific messages. For example, a macro that reads files, processes data, and writes results can allocate estimated shares to those phases, provided the estimates are based on the real workload. Alternatively, reset the bar for each phase and say “Phase 2 of 3.” Do not display a precise remaining-time estimate unless the process can support it; a percentage is not a clock.

Update the status text with useful context, but avoid rewriting it unnecessarily. “Validating invoice 38 of 120” is more informative than “Working.” If the task has no known total, a determinate percentage is misleading. Use a status message or a simple animated indicator instead, and explain that completion cannot be estimated accurately.

Common problems and fixes

The form appears frozen: make sure the macro calls DoEvents during its loop and does not spend a long time inside one blocking operation. A single lengthy database call or calculation cannot be made responsive just by updating a label before and after it.
The bar exceeds its track: check that lblFill is a child of fraTrack, inspect its starting position, and adjust the border allowance in the width calculation. Ensure the completion count never exceeds the total, even though the procedure clamps its display value.
The percentage never changes: confirm that UpdateProgress is called repeatedly and that the loop reaches the update condition. If progress only updates at the end, the underlying work may be in one long operation rather than a loop the macro can report between steps.
The form stays open after an error: use a cleanup path like the example’s error handler. If your macro changes other application settings, such as calculation mode or events, save their original values and restore them in the same cleanup path.

Alternatives to a UserForm

For a simple macro, Excel’s status bar may be enough. Setting Application.StatusBar to a message avoids designing a form, although it does not create a graphical fill bar. If you use the status bar, restore control to Excel when the macro finishes by setting Application.StatusBar = False, including in error cleanup.

A worksheet-based bar is another option when users should see progress in a workbook cell or shape. It is less separate from the data and may be obscured by screen updates, but it can be convenient for a dashboard or a process users need to monitor alongside worksheet content. A UserForm is preferable when the progress display should stay prominent and independent of the active sheet.

Excel for the web does not run VBA macros, so a VBA UserForm progress bar will not work there. Excel for Mac has VBA support, but form-control behavior and the surrounding development experience can differ from Windows; test a form-based solution on the Mac versions your users actually have. Avoid relying on Windows-specific APIs for a portable macro.

Frequently Asked Questions

Does Excel VBA have a built-in progress bar?
No standard UserForm progress-bar control is required or assumed here. A common solution uses a Label inside a Frame and changes the Label’s width as work completes. Third-party or legacy controls may exist, but can add setup and compatibility concerns.

Why does my UserForm progress bar not update until the macro ends?
VBA may be busy running code without returning to Excel’s event loop. Show the form modelessly and call DoEvents periodically from the work loop. If one individual operation blocks for a long time, the form cannot provide intermediate updates during that operation.

Can I show a progress bar without a UserForm?
Yes. You can write progress messages to Excel’s status bar with Application.StatusBar, or display progress in a worksheet cell or shape. These options avoid a separate window but do not provide the same self-contained UserForm interface.

Can the user cancel the macro from the progress form?
Yes, but cancellation needs explicit code. Add a Cancel button, record the request in a form-level Boolean variable, and have the loop check that variable between units of work. Clean up the form and restore application settings when cancellation is detected; do not rely on closing a modeless form alone to safely stop the procedure.

Will a VBA UserForm progress bar work in Excel for the web?
No. Excel for the web does not execute VBA, so it cannot display a VBA UserForm or run the macro that updates it. Users need a supported desktop version of Excel to run this approach.

Safe use and practical limits

A progress form is presentation, not protection against interrupted or partial work. If the macro edits important data, design the operation so errors can be detected and reported, and consider working on a copy or staging results before replacing original data. Save a separate copy of the workbook before testing because macro changes cannot normally be undone with Ctrl+Z. Finally, keep the form’s promises modest: it can show the macro’s reported progress, but it cannot guarantee that the remaining work will take a proportional amount of time.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.