Microsoft Office Tutorials and References
In Depth Information
Displaying a Progress Indicator
Figure 15-4: This UserForm will serve as a progress indicator.
You can, of course, apply any other type of formatting to the controls. For example, I changed
the SpecialEffect property for the Frame control to make it “sunken.”
Creating the event-handler procedures for the stand-alone progress indicator
The trick here involves running a procedure automatically when the UserForm is displayed. One
option is to use the Initialize event. However, this event occurs before the UserForm is
actually displayed, so it’s not appropriate. The Activate event, on the other hand, is triggered when
the UserForm is displayed, so it’s perfect for this application.
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 modified version of the GenerateRandomNumber procedure (which was presented earlier)
follows. Notice that 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
 
Search JabSto ::




Custom Search