How to create a custom progress bar?

If you want to create a custom progress bar while executing a macro automatically in Excel, here you will learn how to do it. The progress bars help the user to know how long he is going to take a routine, they also help to know where the routine is going.

How to Create a Custom Progress Bar While Running a Macro in Excel

This is why, sometimes you need to place a custom progress bar while executing a macro or UserForm in Excel . Without further ado, it is time for you to learn quickly and easily how to achieve it step by step.

How to create a custom progress bar?

The personalized progress bars are a luxury that will make your work in Excel stand out for your excellent handling of the tools. Also, you will get compliments every time you place a custom progress bar while creating macros in Excel because your work will be more explicit.

The only disadvantage that you should consider is that by placing this tool, the Excel program will consume more computer resources. Now that you know this, it is time for you to learn how to create or add a custom progress bar while running a macro in Excel by following these steps:

Step 1: Create the progress bar.

1. Open the Excel program, click on the “Developer” tab and select “Visual Basic.”
2. In the next window, choose the “Insert” tab and in the submenu that will open, click on ” UserForm “.
3. Now, in the UserForm controls window, you select the “Frame”, “Label” and “commandButtom” controls and place them inside the ” UserForm ” window .
4. Next, you can customize your macro, in the left menu you can put the color you want to the UserForm and the caption of the progress bar, among other details to customize.
5. After making the customizations of your macro, drag the “Label” bar inside the “Frame” bar, now you must customize the color of the Label bar, both the border and the inside.
6. Then, you are going to drag the border of the “Frame” bar to the border of the “Label” bar to match them in the same size and place them in a corner of your macro.
7. You change the name of the ” CommandButtom ” bar to ” Cancel ” and place it below the ” Frame ” bar to make your macro look more orderly (This button will be used to cancel the routine if the user so wishes).

Following these steps you will have the progress bar that you will use in Excel ready, now you will have to change the programming codes so that it is personalized.

Step 2: Customize how the progress bar works with codes.

1. Click on the button that you renamed with the name «Cancel» to configure its operation and put the following: End, you hit the «Enter» key and on the next line you write down Unload Me.
2. Next, you select the “UserForm” tab located in the left menu and click on the macro.
3. In the next window, choose the upper right tab that says “Click”, a submenu will open and select ” Activate “.
4. Now you are going to write down the following commands:

Dim Count As Long

Dim nRows As Long

Dim nColumns As Long

Dim F As Long

Dim C As Long

Dim Percentage As Double

Count = 1

Rows = 500

nColumns = 50

For F = 1 To nRows

For C = 1 To nColumns

Cells (f, c) = Count

Count = Count + 1

Next C

Percentage = Count / (nRows * nColumns)

1. Caption = Format (Percentage, “0%”
2. Label.Width = Percentage * Me.Frame.Width

DoEvents

Next F

Unload me

5. After having placed all the commands, you click on the « Save« button .
6. Select the ” UserForm” tab and choose the “Label” bar to set its “Width” to zero.
7. To finish you click on the “Save” button.

Following these simple steps, you will have your personalized progress bar ready while a macro is running in Excel. In the following illustration you will see how the commands should be noted in the programming.

Also, you may be interested in how to use form controls to create budget templates in Excel. If you loved this post on how to create a custom progress bar while running a macro in Excel follow our blog.