We can use VBA progress bar to keep track of running macros, especially when our running macro is taking a lot of time to finish. Without the progress bar, we may wonder if the macro is still running. In this tutorial, we will learn how to create custom VBA progress bars in excel.
Creating a Userform
We will first create a Userform and add elements that can turn it into a progress bar. We will follow these steps
- We will open the VBA editor by clicking Alt +F11, or we can also go to the Developer Tab and select Visual Basic
Figure 1 – How to create a progress bar in excel
- In the Visual Basic Window, we will right-click in the Project Explorer Pane and click Insert to view and click Userform.
Figure 2 – Creating a progress bar in excel
Set Progress Bar properties
We can carry out these steps before adding labels and frame, but in this case, we will change the Userform properties now.
- We will right-click on the Userform and click Properties
Figure 3 – Excel VBA status bar progress
- In the Userform1 properties window, we will change the name of the Userform to ProgressBar and set the height to 100. We will also set caption to display Progress
Figure 4 – Excel VBA status bar
- We will change the ShowModal property to False; this ensures that all other macros will continue to run even Userform is running. We will also set the width of the Userform at 240.
Figure 5 – Making an Excel VBA application.status bar
Adding Userform properties
The Userform should have three controls. Therefore, we will insert two label controls and frame control.
Add the first Label Control into the Frame Control
- In the toolbar, we will select Label
Figure 6 – Application status bar in VBA
- Next, we will drag to draw label in Userform
Figure 7 – VBA update status bar
- Again, we will right click on the label control and select properties
Figure 8 – Creating an excel VBA status bar
- We will change the name of Label 1 to CapLabel. We will also set the height at 50 and set the Caption as an empty string.
Figure 9 – Progress bar in Excel
- We will also set the width of this label to 162.
Figure 10 – Creating Status bar in VBA
We will add a frame control to the Userform
- We will click on Frame in the Tool Box
Figure 11 – Making a VBA update status bar
- Next, we will drag frame control on the Userform.
Figure 12 – Making an excel VBA status bar
- To add properties to the frame control, we will right click on the frame control and select properties.
Figure 13 – Making Excel VBA status bar
- Next, we will erase all content in the Caption field and Change the name of our Frame to ProgressFrame
Figure 14 – Excel VBA Status progress
- Next, we will change special effect property to 2-FmspecialEffectSunken
Figure 15 – Creating VBA progress bar
Add 2nd Label to Userform
We will insert a second Label directly inside the first label.
- We will select another Label and insert into the first label.
Figure 16 – Making a VBA progress bar
- In the property tab, we will;
- Make the caption field empty
- Change the name of the property to ProgressIndicator
- Change the BackColor to Highlight
Figure 17 – Making a VBA progress bar
- We will change the special effect property to 3-fmSpecialEffectRaised
Figure 18 – Using a VBA update status bar
Add Macro code to Userform
- We will right-click anywhere on the Userform and click View Code
Figure 19 – How to create a progress bar in excel
- We will go back to the userform, right-click and select View code
Figure 20 – Progress bar in excel
- Next, we will display the progress bar by entering this macro code in the open window
Private Sub UserForm_Initialize()
#If IsMac = False Then
Me.Height = Me.Height - 10
HideTitleBar.HideTitleBar Me
#End If
End Sub
Figure 21 – VBA update status bar
- We use the IsMac if conditional code so that users running Excel on Mac can also use this macro code without crashing.
- We will click on Insert and select Module
- Here, we will place this code
Sub LoopThroughRows()
Dim i As Long, lastrow As Long
Dim pctdone As Single
lastrow = Range("A" & Rows.Count).End(xlUp).Row
ProgressBar.ProgressIndicator.Width = 0
ProgressBar.Show
For i = 1 To lastrow
pctdone = i / lastrow
With ProgressBar
.CapLabel.Caption = "Processing Row " & i & " of " & lastrow
.ProgressIndicator.Width = pctdone * (.ProgressFrame.Width)
End With
DoEvents
If i = lastrow Then Unload ProgressBar
Next i
End Sub
Figure 22 – excel vba progress bar
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment