Create Progress Bar in UserForm with Excel VBA

Step 1: Create a UserForm

To begin, we need to create a UserForm where the progress bar will be displayed.

1. Open the VBA Editor: Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in Excel.
2. Insert a UserForm:
   • In the VBA editor, go to Insert > UserForm to create a new UserForm.
3. Rename the UserForm (optional):
   • In the properties window, you can rename the form to something meaningful like frmProgressBar.

Step 2: Add a ProgressBar Control

In Excel VBA, there isn’t a built-in ProgressBar control, but you can use a Label to simulate one.

1. Insert a Label for the Progress Bar:
   • In the UserForm, insert a Label control by clicking the Label button from the toolbox.
   • Resize the label to act as the background of the progress bar (e.g., 200px wide and 20px tall).
   • Set the BackColor of the label to a light color (like Gray or LightGray).
   • Name this label lblProgressBar.
2. Insert another Label for the Progress Indicator:
   • Insert another Label control that will represent the actual progress bar.
   • Set the BackColor of this label to a vibrant color (e.g., Green, Blue, etc.).
   • Name this label lblProgress.
   • Set the initial width of this label to 0 to start with no progress.

Step 3: Code the Progress Bar

Now, we’ll write the code to update the progress bar. We’ll create a subroutine to update the progress, which will be called by the main macro.

1. Add the UpdateProgressBar Sub:

Add a subroutine in the UserForm’s code to update the width of the progress label (lblProgress) based on a percentage value.

Sub UpdateProgressBar(ByVal Progress As Double)
    ' Progress ranges from 0 to 100
    Dim ProgressWidth As Double
    ProgressWidth = (Progress / 100) * lblProgressBar.Width
    lblProgress.Width = ProgressWidth
End Sub

In this subroutine:

• Progress is the percentage value that you pass to the subroutine to represent how much of the task is complete (from 0 to 100).
• The width of lblProgress is updated based on the percentage. The width of lblProgressBar is used to scale the progress.

Step 4: Call the UpdateProgressBar Sub

You need to call this UpdateProgressBar subroutine from a macro or another subroutine where you want to show the progress.

1. Create a Button or Triggering Event:
   • Insert a button (or any control) that triggers the operation you want to track.
2. Code the Progress Tracking in a Sub: In the main macro, simulate a task that takes time, updating the progress bar as it goes. For example, if the task is a loop, you can update the progress bar each time an iteration is completed.

Sub StartTask()
    Dim i As Integer
    Dim TotalSteps As Integer
    TotalSteps = 100  ' For example, if the task has 100 steps   
    ' Show the UserForm
    frmProgressBar.Show vbModeless   
    ' Loop through each step of the task
    For i = 1 To TotalSteps
        ' Simulate some work
        DoEvents  ' This allows the form to update during the loop       
        ' Update the progress bar
        frmProgressBar.UpdateProgressBar (i)
    Next i   
    ' Close the UserForm when done
    Unload frmProgressBar
End Sub

Explanation of the Code:

• frmProgressBar.Show vbModeless: This opens the UserForm in a non-blocking (modeless) manner, allowing other actions (like updating the progress bar) to happen while the task is running.
• DoEvents: This allows Excel to update the UserForm during the execution of the loop. Without it, the progress bar might not be updated during the loop because Excel might be frozen while executing the code.
• frmProgressBar.UpdateProgressBar (i): This updates the progress bar with the current percentage i.
• After the loop completes, the form is unloaded (Unload frmProgressBar), closing the progress bar.

Output:

• When you run the StartTask subroutine, the UserForm appears with the progress bar. As the loop progresses, the lblProgress label width increases, simulating the progress of the task. When the task is completed, the progress bar disappears.

Conclusion:

This method uses basic VBA controls to simulate a progress bar. It’s a flexible solution that can be adapted to various tasks, from loops to long-running processes, by simply updating the progress bar at regular intervals using the UpdateProgressBar subroutine.