VBA Progress Bar in Excel
A progress bar in VBA excel shows the percentage of task completed when the actual task running behind the screen with a set of instructions given by the VBA code.
When the VBA code takes a considerable amount of time to execute, it is the user anxiety to know how soon it can finish. By default, we need to wait for the full time to complete the task but by inserting the progress bar we get to know the progress of the VBA code.
In almost all the computer software’s we see a progress bar chart which shows the progress of the task we are performing just like the below image.
In excel so we can create Progress Bar by using VBA coding. If you have ever wondered how we can create a progress bar then this is the article tailor-made for you. Follow the article to have your own VBA progress bar in Excel.
Create Your Own Progress Bar in Excel VBA
In order to create a Progress Bar Using VBA Code, we need to follow several steps. Below are the steps to involve while creating the Progress Bar chart in VBA.
Step 1: Create or Insert new User form.
As soon as you click on the option above you will see a user form like the below.
Step 2: Press F4 key to see the VBA properties window.
In this properties tab, we need to change the properties of the VBA userform we have inserted.
Step 3: Change the name of the user form to UFProgressBar.
Now we can refer this user form with the name “UFProgressBar” while coding.
Step 4: Change the Show Model Property of the user form to FALSE.
Step 5: Now adjust the alignment of the user from to fit your needs. I have changed the Height of the user form to 120 and width to 300.
Step 6: Change the Caption of the user form to “Progress Status Bar”.
Step 7: Now from the toolbox of the user form insert LABEL to the user form at the top.
With this label, we need to set the properties of the label. Delete the caption and make it blank and adjust the width of the label.
Step 8: Change the Name of the label to “ProgessLabel”.
Step 9: Now from the toolbox take the frame and draw just below the label we have inserted in the previous steps. Make sure the frame is at the center of the user form.
Step 10: Now we need to change some of the properties of the Frame to make it look exactly the same as the user form 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 Special Effect of the frame to 6 – fmSpecialEffectBump.
After all these changes our user form 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 make the sure 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 properties of the label 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.
Ok, now we are done with the process of setting up the VBA progress bars in excel and at this point in time, 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 excel.
Sub InitUFProgressBarBar() With UFProgressBar .Bar.Width = 0 .Text.Caption = "0%" .Show vbModeless End With
Now if you run this code manually or through F5 key, we should see the progress bar like this.
Step 14: Now we need to create a macro to perform our task. I am performing the task of inserting serial numbers from 1 to 5000. Along with this code, we need to configure the progress bar chart as well, below code is the 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 has been a guide to VBA Progress Bar. Here we learn how to Create Progress Bar Chart using VBA Code in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –