Stand-alone Progress indicator UserForm

Building the stand-alone progress indicator UserForm

Follow these steps to create the UserForm that will be used to display the progress of your task:

1.       Insert a new UserForm and change its Caption property setting to Progress.

2.       Add a Frame control and name it FrameProgress.

3.       Add a Label control inside the Frame, name it LabelProgress, remove the label’s caption, and make its background color (BackColor property) something that will stand out.

4.       Add another label above the frame to explain what’s going on (optional).

5.       In this example, the label reads, Cell Updation in Progress

6.       Adjust the UserForm and controls so that they look something like below Figure.

Insert the following procedure in the code window for the UserForm. This procedure simply calls a procedure named GenerateRandomNumbers when the UserForm is displayed. This procedure, which is stored in a VBA module, is the actual macro that runs while the progress indicator is displayed.

Private Sub UserForm_Activate()

Call GenerateRandomNumbers

End Sub

The GenerateRandomNumber procedure with additional code keeps track of the progress and stores it in a variable named PctDone.

Sub GenerateRandomNumbers()

‘ Inserts random numbers on the active worksheet

Dim Counter As Long

Const RowMax As Long = 500

Const ColMax As Long = 40

Dim r As Integer, c As Long

Dim PctDone As Double

If TypeName(ActiveSheet) <> “Worksheet” Then Exit Sub

Cells.Clear

Counter = 1

For r = 1 To RowMax

For c = 1 To ColMax

Cells(r, c) = Int(Rnd * 1000)

Counter = Counter + 1

Next c

PctDone = Counter / (RowMax * ColMax)

Call UpdateProgress(PctDone)

Next r

Unload UserForm1

End Sub

The GenerateRandomNumbers procedure contains two loops. Within the inner loop is a call to the UpdateProgress procedure, which takes one argument (the PctDone variable, which represents the progress of the macro). PctDone will contain a value between 0 and 100.

Sub UpdateProgress(Pct)

With UserForm1

.FrameProgress.Caption = Format(Pct, “0%”)

.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)

.Repaint

End With

End Sub

Creating the start-up procedure for a stand-alone progress indicator, all that’s missing is a procedure to display the UserForm. Enter the following procedure in a VBA module:

Sub ShowUserForm()

With UserForm1

.LabelProgress.Width = 0

.LabelProgress.BackColor = ActiveWorkbook.Theme. _

ThemeColorScheme.Colors(msoThemeAccent1)

.Show

End With

End Sub