Showing a progress indicator by using a Multi Page control

In the preceding example, the macro was not initiated by a UserForm. 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:

■ Your project is completed and debugged.

■ Your project uses a UserForm (without a MultiPage control) to initiate a lengthy macro.

■ You have a way to gauge the progress of your macro.

CD- The companion CD-ROM contains an example that demonstrates this technique. The file is ROM 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).

A

1

2 1

l>fin'i-oni r-'iii'b»! _t '

i

h

Lnk.i'r 7T ipsr zt m T-J iEL- T. ta 11 r»+

7 |

* 1

LI

III^lJ

iii_.- - i

L3

L<

« ■ • f Will

r

T

Figure 15-5: The user specifies the number of rows and columns for the random numbers.

Figure 15-5: The user specifies the number of rows and columns for the random numbers.

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.

The first step is to add a MultiPage control to your UserForm. Then move all the existing controls on the UserForm and paste them to Page1 of the MultiPage control.

Next, activate Page2 of the MultiPage control and set it up as shown in Figure 15-6. This is essentially the same combination of controls used in the example in the previous section.

1. Add a Frame control and name it FrameProgress.

2. Add a Label control inside the Frame and name it LabelProgress. Remove the label's caption and make its background color red.

3. Add another label to describe what's going on (optional).

4. Next, activate the MultiPage control itself (not a page on the control) and set its Style property to 2 - fmTabstyleNone. (This will hide the tabs.) You'll probably need to adjust the size of the MultiPage control to account for the fact that the tabs are not displayed.

Tip The easiest way to select the MultiPage control when the tabs are hidden is to use the dropdown list in the Properties window.

INSERTING THE UPDATEPROGRESS PROCEDURE FOR A PROGRESS INDICATOR WITH A MULTIPAGE CONTROL

Insert the following procedure in the code module for the UserForm:

Sub UpdateProgress(Pct) With UserForm1 .FrameProgress.Caption = Format(Pct, "0%") .LabelProgress.Width = Pct * (.FrameProgress.Width - 10) .Repaint End With End Sub

The UpdateProgress procedure is called from the macro that's executed when the user clicks the OK button, and it performs the updating of the progress indicator.

MODIFYING YOUR PROCEDURE FOR A PROGRESS INDICATOR WITH A MULTIPAGE CONTROL

You need to modify the procedure that is executed when the user clicks the OK Button - the Click event handler procedure for the button named OKButton_Click. First, insert the following statement at the top of your procedure:

MultiPage1.Value = 1

This statement activates Page2 of the MultiPage control (the page that displays the progress indicator).

In the next step, you're pretty much on your own. You need to write code to calculate the percent completed and assign this value to a variable named PctDone. Most likely, this calculation will be performed inside of a loop. Then insert the following statement, which will update the progress indicator:

Call UpdateProgress(PctDone)

HOW A PROGRESS INDICATOR WITH A MULTIPAGE CONTROL WORKS

This technique is very straightforward and, as you've seen, it involves only one UserForm. The code switches pages of the MultiPage control and converts your normal dialog box into a progress indicator. Because the MultiPage tabs are hidden, it doesn't even resemble a MultiPage control.

0 0

Post a comment