Changing a UserForm s Size

Changing a UserForm's 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-REFERENCE  

Refer to Chapter 15 for an example that allows the user to change the 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.

Figure 14-5: A sample dialog box in its standard 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"     Else         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-ROM  

The example in this section is available on the companion CD-ROM. The file is named  change userform size.xlsm .