Using a User Form as a progress indicator

One of the most common Excel programming questions I hear is "How can I make a UserForm display the progress of a lengthy macro?"

Use Excel's custom dialog box to easily create an attractive progress indicator, as shown in Figure 18-9. Such a use of dialog boxes does, however, require a few tricks — which I'm about to show you.

Figure 18-9:

This UserForm functions as a progress indicator for a lengthy macro.

Figure 18-9:

This UserForm functions as a progress indicator for a lengthy macro.

Creating the progress indicator dialog box

The first step is to create your UserForm. In this example, the dialog box displays the progress while a macro inserts random numbers into 25 columns and 100 rows of the active worksheet. To create the dialog box, follow these steps:

1. Activate the VBE and insert a new UserForm.

2. Change the UserForm's caption to Progress.

3. Add a Frame object and set the following properties:

Property Value

Caption 0%

Name FrameProgress

SpecialEffect 2 — fmSpecialEffectSunken Width 204

Height 28

4. Add a Label object inside the Frame and set the following properties:






&H000000FF& (red)


(no caption)


1 — fmSpecialEffectRaised









5. Add another Label above the frame and change its caption to Entering random numbers. . . .

The UserForm should resemble Figure 18-10.

Figure 18-10:

The progress indicator UserForm.

The procedures

This example uses four procedures.

i Start. Located in a VBA module, Start is the entry procedure that is executed to get things going:

Sub Start()

' The UserForm1_Activate sub calls Main UserForml.LabelProgress.Width = 0 UserForml.Show End Sub

Figure 18-10:

The progress indicator UserForm.

Userform Visual Basic Excel

1 UserForm_Activate. This procedure, located in the Code window for the UserForm object, is executed when the UserForm is displayed. This procedure simply calls another procedure called Main:

Private Sub UserForm_Activate()

Call Main End Sub

1 Main. It does all the work and is executed when the UserForm is shown. Notice that it calls the UpdateProgress procedure, which updates the progress indicator in the dialog box:



Inserts random numbers on the active worksheet

Dim Counter As Integer

Dim RowMax As Integer, ColMax As Integer

Dim r As Integer, c As Integer

Dim PctDone As Single

If TypeName(ActiveSheet) <> "Worksheet" Then

Unload UserForml

Exit Sub

End If Cells.Clear

Counter = 1

RowMax = 100

ColMax = 25

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



1 UpdateProgress. This procedure accepts one argument and updates the progress indicator in the dialog box:



With UserForm1

.FrameProgress.Caption = Format(pct, "0%")

.LabelProgress.Width = pct * (.FrameProgress

.Width - 10)

End With

The DoEvents statement is responsible for the

form updating




How this example works

When the Start procedure is executed, it sets the width of the LabelProgress label to 0 and then shows the UserForm, which triggers an Activate event for the UserForm. The UserForm_Activate( ) procedure is executed, which in turn executes the Main procedure.

The Main procedure checks the active sheet. If it's not a worksheet, the UserForm is closed and the procedure ends with no action. If the active sheet is a worksheet, the procedure does the following:

1. Erases all cells on the active worksheet.

2. Loops through the rows and columns (specified by the RowMax and ColMax variables) and inserts a random number.

3. Increments the Counter variable and calculates the percentage completed (which is stored in the PctDone variable).

4. Calls the UpdateProgress procedure, which displays the percentage completed by changing the width of the LabelProgress label.

5. Unloads the UserForm.

If you adapt this technique for your own use, you need to figure out how to determine the macro's progress, which varies, depending on your macro. Then call the UpdateProgress procedure at periodic intervals while your macro is executing.

This example is available at this book's Web site.

Was this article helpful?

+1 0


  • mira
    How to create progress indicator using vba excel macro?
    1 year ago

Post a comment