Create a progress bar in Excel using VBA (Visual Basic for Applications)

Progress bar

In this article we will create one progress bar using Excel vba with step by step example. User can change feel and look of the UserForm as per their need and mimic the logic to fit in their solution.

Design

1. Insert a user form
2. Add one label on it and change Caption property as “Please wait…“
3. Add one Group Frame control on the form below to label created above and remove caption and set Name property as “progressFrame“
4. Add one label inside frame and remove Caption then set name property as “lblProgress” and set background color as per your choice

Code behind

1. Insert a module and name it as “modProgress”
2. Double click on the user form and put following code on the Activate event of the form

lblProgress.Width = 0
Call Change

Put following code in modProgress module:

Public Sub Change()
    Dim Counter As Integer
    Dim maxRow As Integer, maxCol As Integer
    Dim r As Integer, c As Integer
    Dim percentCompleted As Single

Application.ScreenUpdating = False
    ' Initialize variables.
    Counter = 1
    maxRow = 100
    maxCol = 25

' Loop through cells.
    For r = 1 To maxRow
        For c = 1 To maxCol
            'Put a random number in a cell
            Cells(r, c) = Int(Rnd * 1000)
            Counter = Counter + 1
        Next c

' Update the percentage completed.
        percentCompleted = Counter / (maxRow * maxCol)

' Call subroutine that updates the progress bar.
        UpdateProgressBar percentCompleted
    Next r
    ' The task is finished, so unload the UserForm.
    Unload UserForm1
End Sub

Sub UpdateProgressBar(percentCompleted As Single)
    With UserForm1

' Update the Caption property of the Frame control.
        .progressFrame.Caption = Format(percentCompleted, "0%")

' Widen the Label control.
        .lblProgress.Width = percentCompleted * _
            (.progressFrame.Width - 10)
    End With

' The DoEvents allows the UserForm to update.
    DoEvents
End Sub

Run the User form and you will have your progress bar as shown below:

Output

Next >> Get count of cells based on color using VBA code example

Share on Facebook

Tweet