How to create a custom progress bar while a macro is running in Excel

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

This is why sometimes you need to place a custom progress bar while a macro or UserForm is running 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 due to its excellent use 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’s time for you to learn how to create or add a custom progress bar while a macro is running 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, 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. Next, you’re going to drag the edge of the “Frame” bar to the edge of the “Label” bar to make them the same size and place them in one corner of your macro.
7. You rename the “CommandButtom” bar to “Cancel” and place it below the “Frame” bar to make your macro look neater (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 must change the programming codes so that it is personalized.

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

1. Click the button that you renamed with the name “Cancel” to configure its operation and place the following: End, you hit the “Enter” key and on the next line you write down Unload Me.
2. Next, 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 Ace Long

Dim nRows As Long

Dim nColumns As Long

Dim F As Long

Dim C As Long

Dim Percent 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

Percent = Count / (nRows * nColumns)

I. Caption = Format (Percentage, “0%”

I. Label.Width = Percentage * Me.Frame.Width

DoEvents

Next F

unload me

5. After having placed all the commands, you click the “Save” button.

6. Select the “UserForm” tab and choose the “Label” bar to set its “Width” to zero.

7. To finish you click the “Save” button.

By following these simple steps you will have your custom progress bar ready while a macro is running in Excel. In the following illustration you can see how the commands noted in the programming should look like.

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