How to display a progress bar with a user form in Excel

Progress Bar

In this post, we are going to learn how to display a progress bar with a UserForm in Excel and VBA.

Role of Progress Bar

If you have a VBA based automated Tracker, Dashboard or Application that takes long time to finish the activity then indication of progress is mandatory otherwise, user will not be able to understand whether any task is being performed or system is not responding.

With the help of progress bar, user can follow the progress of a lengthy operation and get the information about ongoing operation and how much approximate percentage of task has been done.

So, considering the usability of progress bar, we must include it in the complex automation or time taking VBA based jobs where we need to show the progress of a task.

Demo of Progress Bar

Progress Bar
Progress bar demo

As progress bar is to show the completion % of a task, I have added a button on home page so that whenever user will click on it, it will insert 100 new worksheets, rename all the worksheets and add 10 different columns headers in the first row of every sheet.

In preforming all these activities, it will take time some time hence we can show the completion% with the help of progress bar for the demo purpose.

So, this is just an example to show the progress bar. You can utilize the same logic in your actual project.

Creating the User Form for Progress Bar in VBA

  1. Open a new workbook in Excel.
  2. Save the file with the name “Progress bar” with macro enabled extension.
  3. Rename the Sheet1 to ‘Home’ and insert a rounded rectangle with caption ‘Insert Worksheets’.
  4. Move to the Visual Basic Application window. To do that, just click on Developer Tab and then click on VBA button available in Code group. Alternatively, you can press ALT + F11 as shortcut key.
  5. On the Insert menu, click UserForm.
  6. Change the following properties of UserForm:
    Name: frmProgressForm | Caption: Progress | Height: 86 | Width: 278 | ShowModal: False
  7. Insert a Frame control on UserForm.
  8. Change the following properties of the Frame control to the following values:
    Name: FrameProgress | Caption : 0% | Height : 42 | Width : 252
  9. Insert a Label control on UserForm in Frame.
  10. Change the following properties of the Label control to the following values:
    Name: lblProgress | Caption : NULL | BackColor: Blue | Height : 18 | Width : 240 | SpecialEffect:1-frmSpecialEffectRaised

Now, we have done with designing the form and progress bar.

Writing VBA Code

Let’s inset a module to write the code. To insert a module, click on Insert menu and then click on module.

Let’s move to code window of module.

Here, we need write a sub procedure to insert 100 new worksheets in the workbook and rename all the sheets with naming pattern ‘Custom Sheet 1…2…3…” and then create 10 column headers in all the newly inserted sheets.

While performing all the operations, we will calculate the completion % and then increase the width of label to show the progress of task.

Let us write a sub procedure to show the form. We will utilize this while assigning macro on the button available on Home worksheet.

Now, let’s move the form code widow and call the ‘ProcessActivity’ procedure on form activate event.

Now, we have done with designing and coding the progress bar UserForm. Let us move to Excel window and assign the macro on the button.

To assign the macro, let us right click on the button and then click on ‘Assign Macro…’ Select the ‘Show_Form’ from the Macro name and then click on Okay.

Assigning macro

Now, we have done with macro assignment. You can click on Button to insert sheets and show the progress bar.

Please watch step by step YouTube tutorial to develop Progress Bar in Excel and VBA.

So, this is all about the Progress bar in Excel and VBA. If you have any question you can always ask me in comment section or send us an email on info@thedatalabs.org

Click on below button to download the practice file along with VBA code.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here