Its a basic progress bar that determines the progress of a process in VBA. You can use it in any way you want.
Follow these steps.
1) In the VBA editor, open Project Explorer window, right click the project to insert a UserForm.
2) Next, add a Label control from the toolbox.
3) Right click UserForm and select Properties
We'll have to change the default properties of the UserForm and Label as per our requirement.
* In the "properties" window, clear the caption of the userform. We'll keep the caption of the form empty and it will be changed dynamically (at runtime) using a code (macro).
* Set the Width property to 200.
Now, let's set the label properties. Right click the label and choose "Properties".
* Remove or clear the caption.
* Set a minimum value for the label. We'll assign the width at runtime using a macro.
The design is ready. We'll write the macro the display the progress bar.
Double click Userform or press F7 to open the code window. Add the below code in userform.
Option Explicit Private Sub UserForm_Activate() showProgress End Sub Sub showProgress() Application.ScreenUpdating = False UserForm1.Label1.Caption = "" Clear the 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
You can open the userform or should I say, the progress bar from anywhere you want. I'll open the progress bar from a worksheet with click of a button. Therefore, I'll write this in the button's click event.
Private Sub cmd_Click() UserForm1.Show ' Open userform to show the progress bar. End Sub
The width of Label is dynamically changed inside the for loop. It creates a progress bar effect.
The UserForm caption also shows the progress in % (percent). See the image in the beginning.