How can we control the size of a UserForm in Excel? A detailed explanation of the various manual and automatic methods of changing the size of a user-form is described in this interesting article by Microsoft.
Watch our training video which demonstrates how to change the size of a user-form in Excel using VBA:
You may like to watch this video on YouTube.
1. We insert a list-box on the user-form and initialize it with the following code:
Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ListBox1.AddItem ws.Name
Next ws
Me.Height = 120
End Sub
The above code gets us all the names of the worksheets in our workbook into the ListBox1.
2. In the properties area of the ListBox1 we set:
a. ListStyle to 1-fmListStyleOption
b. MultiSelect to 1-fmMultiSelectMulti
We next add two Option-Buttons with the labels Portrait and Landscape to indicate the print mode. We set the value property of OptionButton1 (portrait) to ‘True’ so that this option-button is automatically selected when the user-form fires up.
We also insert a check-box with the label ‘print gridlines’ to be able to print the gridlines on selection.
We have 3 command buttons called ‘More Options’, ‘Print’ and Cancel.
The ‘more options’ command button sets the size of the user-form using the following VBA code:
Private Sub CommandButton1_Click()
If CommandButton1.Caption = “More Options” Then
Me.Height = 172
CommandButton1.Caption = “Options”
Else
Me.Height = 120
CommandButton1.Caption = “More Options”
End If
End Sub
The print command button helps to print the selected sheets in portrait or landscape with or without gridlines:
Private Sub CommandButton2_Click()
Dim i As Long
MsgBox “Only a demo. We won’t print to save paper!”
For i = 0 To ListBox1.ListCount – 1
If ListBox1.Selected(i) Then
With Sheets(ListBox1.List(i))
.PageSetup.PrintGridlines = CheckBox1
If OptionButton2 Then .PageSetup.Orientation = xlLandscape
If OptionButton1 Then .PageSetup.Orientation = xlPortrait
‘.PrintOut
End With
End If
Next i
‘Unload Me
End Sub
We have remarked the ‘PrintOut’ command to avoid unnecessary print-outs.
The ‘cancel’ button unloads the user-form:
Private Sub CommandButton3_Click()
Unload Me
End Sub
We can use a value of 0 and +32,767 for the width and height of the user-form. Higher values may also work depending on the computer configuration.
