Excel VBA Progress Bar in Excel

A progress bar shows the percentage of tasks completed when the actual task is running behind the screen with a set of instructions given by the code.

In almost all computer software, we see a progress bar chart that shows the progress of the task we are performing, just like the below image.

In Excel, we can create a progress bar by using VBA coding. If you have ever wondered how we can create a progress bar, this is the tailor-made article for you.

Create Your Own Progress Bar

To create a progress bar, we need to follow several steps. Below are the steps to involve while creating the progress bar chart.

Step 1: Create or Insert a new UserForm.

As soon as you click on the option above, you will see a UserForm like the one below.

Step 2: Press the F4 key to see the VBA Properties window.

In this Properties tab, we need to change the properties of the VBA UserformVBA UserformIn VBA, userforms are customized user-defined forms that are designed to accept user input in the form of a form. It has various sets of controls to add such as text boxes, checkboxes, and labels.read more we have inserted.

Step 3: Change the name of the UserForm to UFProgressBar.

Now, we can refer to this UserForm with the name “UFProgressBar” while coding.

Step 4: Change the “Show Modal” property of the UserForm to “FALSE.”

Step 5: Now, adjust the user’s alignment to fit your needs. We have changed the height of the UserForm to 120 and the width to 300.

Step 6: Change the “Caption” of the UserForm to “Progress Bar.”

Step 7: Now, from the toolbox of the UserForm, insert LABEL to the UserForm at the top.

With this label, we need to set the properties of the label. First, delete the caption, make it blank, and adjust the width of the label.

Step 8: Change the “Name” of the label to “ProgessLabel.”

Step 9: Take the frame from the toolbox and draw just below the label we inserted in the previous steps. Make sure the frame is at the center of the UserForm.

Step 10: We need to change some of the frame’s properties to make it look the same as the UserForm we have inserted.

Property 1: Change the “Name” of the frame to “ProgressFrame.”

Property 2: Delete the “Caption” and make it blank.

Property 3: Change the “SpecialEffect” of the frame to 6 – fmSpecialEffectBump.

After all these changes, our UserForm should look like this.

Step 11: Now, insert one more label. This time insert the label just inside the frame we have inserted.

While inserting the label, ensure the left side of the label exactly fits the frame we have inserted, as shown in the above image.

Step 12: After inserting the label, change the label’s properties as follows.

Property 1: Change the “Name” of the label to “MainProgressLabel.”

Property 2: Delete the “Caption.”

Property 3: Change the background color as per your wish

Now, we have completed setting up the progress bar. At this point, it looks like this.

Now, we need to enter codes to make this work perfectly.

Step 13: To make the framework add the below macro in excelMacro In ExcelA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more.

Code:

Sub InitUFProgressBarBar()

With UFProgressBar .Bar.Width = 0 .Text.Caption = “0%” .Show vbModeless End With

Now, if you run this code manually or through the F5 key, we should see the progress bar like this.

Step 14: We need to create a Macro to perform our task. We are performing the task of inserting serial numbers from 1 to 5,000. We also need to configure the progress bar chart along with this code. The code is tailor-made code for you.

Sub ProgressBar_Chart()

Dim i As Long Dim CurrentUFProgressBar As Double Dim UFProgressBarPercentage As Double Dim BarWidth As Long

i = 1

Call InitUFProgressBarBar

Do While i <= 5500

Cells(i, 1).Value = i

CurrentUFProgressBar = i / 2500 BarWidth = UFProgressBar.Border.Width * CurrentUFProgressBar UFProgressBarPercentage = Round(CurrentUFProgressBar * 100, 0)

UFProgressBar.Bar.Width = BarWidth UFProgressBar.Text.Caption = UFProgressBarPercentage & “% Complete”

DoEvents

i = i + 1

Loop

Unload UFProgressBar

End Sub

This article is a guide to VBA Progress Bar. Here, we learn how to create a progress bar chart, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles: –

  • VBA ArrayList in ExcelVBA UCase FunctionVBA ENUMInteger in VBA