Create Progress Bar in Your Report - Part 1

A progress bar - showing the % completion of a Macro run - can be added to a workbook. It serves to remind the end-user that a Macro is running (hence Excel is locked), and give some idea of how much longer he/she has to wait. It is suitable for a Macro that processes a large amount of data and thus takes some time to run. See the screenshots below for two examples of progress bar.

Prog. Bar 1:

Prog. Bar 2

The Prog. Bar 1 only shows the progress in % numbers, while the second one also shows an actual bar together with the % numbers. In this post I will show you how to create the first progress "bar" and manipulate its value with Macro. You can download the example file here. Please note that there are two buttons in the example file. Clicking on the left button will activate the first progress bar. See below:

The actual Macro, when the button is clicked, does one task: to loop through the entire list of physicians and hide those lines whose specialty is not "Spec3". The progress bar effectively shows the % of lines being processed at any given moment.

Let's first work on creating a progress bar. Go to Visual Basic Editor, make sure the VBA project representing our example file is the current VBA Project, and then choose Insert -> UserForm. A blank userform and a Toolbox will appear. If you don't see the Toolbox, choose View -> Toolbox. First we set the ShowModal property of the userform to False. This is very important as it allows the Macro to keep running when the userform shows. Then we drag from the Toolbox two Label controls, and land them on the form side by side. Change the Caption and other font-related properties of the labels to what's in the example file. Rename the form to Bar1.

Here is the code:

To show the progress bar, use Bar1.Show. The Macro will keep running. Each time the loop cycles through, % of progress is calculated, and the form will be refreshed if necessary. DoEvents yields to other processes so the Macro can run normally. See the section circled by red pen. When the loop cycle is done, use Bar1.Hide to hide the form. You can also use Unload Bar1 to unload it from the memory.