Changing a User Forms Size

Many applications use dialog boxes that change their own size. For example, Excel's Find and Replace dialog box (displayed when you choose Home Editing Find & Select Replace) increases its height when the user clicks the Options button.

The example in this section 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.

CROSS- Refer to Chapter 15 for an example that allows the user to change the

REFERENCE UserForm's size by dragging the lower-right corner.

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.

Print Sheets

Select the sheets that you want to print.












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

Print Sheets

Select the sheets that you want to print.

□ Sheetl


E3 Sheets

□ Sheet3

i_J Sheet4

□ SheetS


□ SheetS


Portrait mode

P Print gridlines

•'" Landscape mode


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

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.

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.

Note 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.

CD- The example in this section is available on the companion CD-ROM. The file is named %

ROM change userform size.xlsm.


0 0

Post a comment