Microsoft Office Tutorials and References
In Depth Information
Displaying a Progress Indicator
How the stand-alone progress indicator works
When you execute the ShowUserForm procedure, the Label object’s width is set to 0 . Then the
Show method of the UserForm1 object displays the UserForm (which is the progress indicator).
When the UserForm is displayed, its Activate event is triggered, which executes the
GenerateRandomNumbers procedure. The GenerateRandomNumbers procedure contains
code that calls the UpdateProgress procedure every time the r loop counter variable changes.
Notice that the UpdateProgress procedure uses the Repaint method of the UserForm
object. Without this statement, the changes to the label would not be updated. Before the
GenerateRandomNumbers procedure ends, the last statement unloads the UserForm.
To customize this technique, you need to figure out how to determine the percentage completed
and assign it to the PctDone variable. This calculation will vary, depending on your application.
If your code runs in a loop (as in this example), determining the percentage completed is easy. If
your code is not in a loop, you might need to estimate the progress completed at various points
in your code.
Showing a progress indicator by using a MultiPage control
In the preceding example, a UserForm didn’t initiate the macro. In many cases, your lengthy
macro is kicked off when the user clicks the OK button on a UserForm. The technique that I
describe in this section is a better solution and assumes the following:
h Your project is completed and debugged.
h Your project uses a UserForm (without a MultiPage control) to initiate a lengthy
h You have a way to gauge the progress of your macro.
The companion CD-ROM contains an example that demonstrates this technique. The file
is named progress indicator2.xlsm .
Like the previous example, this one enters random numbers into a worksheet. The difference
here is that the application contains a UserForm that allows the user to specify the number of
rows and columns for the random numbers (see Figure 15-5).
Modifying your UserForm for a progress indicator with a MultiPage control
This step assumes that you have a UserForm all set up. You’ll add a MultiPage control. The first
page of the MultiPage control will contain all your original UserForm controls. The second
page will contain the controls that display the progress indicator. When the macro begins
executing, VBA code will change the Value property of the MultiPage control. This will effectively
hide the original controls and display the progress indicator.