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.
Once you are on the Developer tab, click the Visual Basic button. This will open Visual Basic Editor.
With this editor we will be creating a user form that will show a progress bar. From the Insert menu click on UserForm.
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.
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.
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.
Next go to the properties window for Frame1, the frame you just created, and change the value for (Name) to the code below.
Frame1 properties should look like the picture below.
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.
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.
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.
On UserForm1 right click on the form and select View Code. We will need to write some code to get our progress bar working.
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.
Next go to the Insert menu and click on Module.
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.
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.