How to Create a Progress Bar while Running Macros in Excel

Writing macros in Excel is a great way to automate a a bunch of tasks. We have shown you before how to automate Excel with macros or VBA, but this post is about letting you see a progress bar while they run. As you probably already know, when you run macros in Excel you don’t really know when they are done or crashed, until its too late. Having a progress bar while running macros in Excel would be a big help. Here is how you create a user form that shows a progress bar for your macros.

Show a Progress Bar while Running Macros in Excel

For this post we will by using the help of one of Microsoft Support articles, for all the VBA Code. This is easier than me trying to create my own solution, but ours will use pictures to illustrates how to do this.

To begin open up Excel. In this article we will be using Excel 2013, but this should work in 2007 and up. Once you have Excel open go to the Developers tab. If you don’t have tab you can enable the Developer tab from this how to article.

Show a Progress Bar while Running Macros in Excel -1

Once you are on the Developer tab, click the Visual Basic button. This will open Visual Basic Editor.

Show a Progress Bar while Running Macros in Excel -2

With this editor we will be creating a user form that will show a progress bar. From the Insert menu click on UserForm.

Show a Progress Bar while Running Macros in Excel -3

A new user form will be created. Click on the newly created user form to get the Toolbox menu. We will need to create a label that represents the title. From the Toolbox menu click on the A then on the form create the label by clicking and dragging your mouse. You will have something similar the the picture below.

Show a Progress Bar while Running Macros in Excel -4

After creating your first label will will need to configure some of its properties. Locate the properties window, usually embedded in the lower left hand corner. If you do see the properties window you can find it by going to the View menu and click on Properties Window. After you found the window or enabled it, locate the Caption properties and enter the following:

Updating Now. Please Wait...

Label1 properties should look like the picture below.

Show a Progress Bar while Running Macros in Excel -5

Next we will want to create a frame. Go back to UserForm1 and click the Frame button in the Toolbox menu. Next drag the frame on UserForm1. Your UserForm1 should look similar to the picture below.

Show a Progress Bar while Running Macros in Excel -6

Next go to the properties window for Frame1, the frame you just created, and change the value for (Name) to the code below.

frameProgress

Frame1 properties should look like the picture below.
Show a Progress Bar while Running Macros in Excel -7

After you are finished updating Frame1’s properties go back to UserForm1. We will need to create a Label inside of Frame1. Click the label button on the Toolbox menu and create a new label inside Frame1. Your UserForm1 should look like the picture below.

Show a Progress Bar while Running Macros in Excel -8

After you are done creating the new label, we will need to change a few of its properties. Update the labels properties by the code below.

(Name) labelProgress
BackColor &H80000006&
SpecialEffect fmSpecialEffectRaised
Caption

The properties window should look like the picture below.

Show a Progress Bar while Running Macros in Excel -9

When you look back at the user form you should see the changes you created. Make sure to remove the caption from the label too or you’ll have text in your progress bar.

Show a Progress Bar while Running Macros in Excel -10

On UserForm1 right click on the form and select View Code. We will need to write some code to get our progress bar working.

Show a Progress Bar while Running Macros in Excel -11

You will be taken to the code editor for UserForm1. Here enter the following code.

Private Sub UserForm_Activate()
    ' Set the width of the progress bar to 0.
    UserForm1.labelProgress.Width = 0
 
    ' Call the main subroutine.
    Call Main
End Sub

When you are done it should look like the picture below.

Show a Progress Bar while Running Macros in Excel -12

Next go to the Insert menu and click on Module.

Show a Progress Bar while Running Macros in Excel -13

This will create a new module in our workbook.  Copy and paste the following code below into the new model.

Sub ShowUserForm()
    UserForm1.Show
End Sub
 
Sub Main()
    Dim Counter As Integer
    Dim RowMax As Integer, ColMax As Integer
    Dim r As Integer, c As Integer
    Dim PctDone As Single
 
    Application.ScreenUpdating = False
    ' Initialize variables.
    Counter = 1
    RowMax = 100
    ColMax = 25
 
    ' Loop through cells.
    For r = 1 To RowMax
        For c = 1 To ColMax
            'Put a random number in a cell
            Cells(r, c) = Int(Rnd * 1000)
            Counter = Counter + 1
        Next c
 
        ' Update the percentage completed.
        PctDone = Counter / (RowMax * ColMax)
 
        ' Call subroutine that updates the progress bar.
        UpdateProgressBar PctDone
    Next r
    ' The task is finished, so unload the UserForm.
    Unload UserForm1
End Sub
 
Sub UpdateProgressBar(PctDone As Single)
    With UserForm1
 
        ' Update the Caption property of the Frame control.
        .frameProgress.Caption = Format(PctDone, "0%")
 
        ' Widen the Label control.
        .labelProgress.Width = PctDone * _
            (.frameProgress.Width - 10)
    End With
 
    ' The DoEvents allows the UserForm to update.
    DoEvents
End Sub

When you are finished your module should look similar to the picture below.

Show a Progress Bar while Running Macros in Excel -15

The code we just wrote into the module will fill up the current sheet with numbers while UserForm1 is loading. Here is a quick video of this happening.

 

You can adept this code to fit any macro you have to show your users a progress bar over nothing.

Follow me

Ryan Dozier

Just your typical neighborhood geek that loves computers and finding awesome free stuff.
Follow me