Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles VBA PROGRESS BAR How To Create An Excel VBA Progress Bar – Excelchat

How To Create An Excel VBA Progress Bar – Excelchat

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc