1st Method – Using the Status bar
There is a built-in status bar at the bottom left corner of your worksheet.
If you have a macro that runs a long loop, you can embed a small piece of code in between the loop, which can show a message, likeor etc. in the status bar.
Here’s an example.
Option Explicit Private Sub cmd_Click() showProgress End Sub Sub showProgress() Dim iCounter, iRow, iCol As Integer iCounter = 1 iRow = 6 iCol = 1 For iCounter = 1 To 2000 DoEvents Cells(iRow, iCol) = iCounter Application.StatusBar = "Loading. Please wait... " & Round((iCounter / 2000) * 100, "0") & "%" iRow = iRow + 1 If (iRow = 15) Then iCol = iCol + 1 iRow = 6 End If Next iCounter Application.StatusBar = "Data loaded " & Round((iCounter / 2000) * 100, "0") & "%" End Sub
The above process is very simple. I am just running a for loop 2000 times to test the macro.
While the macro is running the loop, a piece of code updates the status bar. Look here…
Application.StatusBar = "Loading. Please wait... " & Round((iCounter / 2000) * 100, "0") & "%"
The built-in StatusBar property is often used to show a message. We can use this to show the progress of a process.
However, “there is a slight problem”. The status bar is located at the bottom left corner of the worksheet. Its hardly visible and can easily be missed.
Therefore, a dialog showing a progress bar at the middle of the worksheet will be ideal in this case. It will be right in front of you and you can see the progress.
2nd Method – Using a UserForm
In the 2nd method, we’ll actually create our own progress bar, using a UserForm.
The progress bar, in this example, is a pop-up window (or a dialog box), which will be displayed at the center of the worksheet.
In the VBA editor, open Project Explorer window, right click the project to insert a UserForm.
Open the Toolbox and add acontrol. The Label is our progress bar. I’ll show you how.
After you have added the Label control, right click the UserForm and select Properties.
We need toof the UserForm and Label that you have inserted.
1) In the Properties window, clear the Caption of the UserForm. We’ll update the caption from the code (macro).
2) Set the Width property to "200" (approx). You can change the width according your choice.
Now, this is important.
Right click the Label control (on the UserForm) and select Properties.
1) Remove or clear the caption
2) Reduce the Width of the Label. The width will be assigned at runtime through the macro. "That's the trick".
That’s it. The design part is ready. Now lets check the code.
Double the UserForm or Press F7 to open View Code window.
Add this code in the UserForm.
Option Explicit Private Sub UserForm_Activate() ShowProgress End Sub Sub ShowProgress() Application.ScreenUpdating = False UserForm1.Label1.Caption = "" Dim iCounter, iRow, iCol As Integer iCounter = 1 iRow = 6 iCol = 1 Dim iMax As Integer iMax = 2000 For iCounter = 1 To iMax DoEvents ' Enter values in the sheet. Application.Worksheets("sheet1").Cells(iRow, iCol) = iCounter ' Update Label width and UserForm caption. UserForm1.Label1.Width = Round((iCounter / iMax) * 100, "0") UserForm1.Caption = "Loading. Please wait... " & Round((iCounter / iMax) * 100, "0") & "%" iRow = iRow + 1 If (iRow = 15) Then iCol = iCol + 1 iRow = 6 End If Next iCounter Me.Hide ' Hide UserForm when process completes End Sub
Open the UserForm from the worksheet.
Option Explicit Private Sub cmd_Click() UserForm1.Show ' Open userform to show the progress bar. End Sub
The width of Label control is dynamically changed within the for loop. It creates a progress bar effect.
UserForm1.Label1.Width = Round((iCounter / iMax) * 100, "0")
This is how we show the progress of the process animatedly as a dialog or a popup window.
The UserForm caption also shows the progress in % (percent).
Run the macro. If everything is done correctly, you’ll see this output.
Happy coding. ☺