Changing a User Forms Size

Many applications use dialog boxes that change their own size. For example, Excel's AutoFormat dialog box (displayed when you choose Format ^ AutoFormat) increases its height when the user clicks the Options button.

This example demonstrates how to get a UserForm to change its size dynamically. Changing a dialog box's size is done by altering the Width or Height property of the UserForm object.

Figure 14-5 shows the dialog box as it is first displayed, and Figure 14-6 shows it after the user clicks the Options button. Notice that the button's caption changes, depending on the size of the UserForm.

Select the sheets that you want to print.

□ 5heet2

Options »




Figure 14-5: A sample dialog box in its standard mode.

Figure 14-5: A sample dialog box in its standard mode.

While you're creating the UserForm, set it to its largest size to enable you to work with the controls. Then use the UserForm_Initialize procedure to set it to its default (smaller) size.

Figure 14-6: The same dialog box enlarged to show some options.

This example displays a list of worksheets in the active workbook and lets the user select which sheets to print. Following is the event handler that's executed when the CommandButton named OptionsButton is clicked:

Private Sub OptionsButton_Click()

If OptionsButton.Caption = "Options >>" Then Me.Height = 164

OptionsButton.Caption = "<< Options"


Me.Height = 128

OptionsButton.Caption = "Options >>" End If End Sub

This procedure examines the Caption of the CommandButton and sets the UserForm's Height property accordingly.

When controls are not displayed because they are outside the visible portion of the UserForm,the accelerator keys for such controls continue to function. In this example, the user can press the Alt+L hot key (to select the Landscape mode option) even if that option is not visible.To block access to nondisplayed controls,you can write code to disable the controls when they are not displayed.

0 0

Post a comment