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.