Self Adjusting Userform Size

Question

The goal is a userform that auto adjusts in size to show all of the visible controls but no extra dead space.

There would be three combo boxes always visible at the top but below those are 26 labels and their associated five option button/checkboxes. These 26 rows will all start hidden and only be visible under certain conditions.

The first of the three combo boxes will state how many of the 26 rows might need to be visible. They however will only be visible if the second combo box says yes.

I am using Excel 2013.

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value > 0 And Me.ComboBox2.Value = "Yes" Then
    Vision
End If
End Sub


Private Sub ComboBox2_Change()
If Me.ComboBox1.Value > 0 And Me.ComboBox2.Value = "Yes" Then
    Vision
End If
End Sub


Private Sub UserForm_Initialize()
With ComboBox1
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
    .AddItem "4"
    .AddItem "5"
    .AddItem "6"
End With

With ComboBox2
    .AddItem "Yes"
    .AddItem "NO"
End With

With ComboBox3
    .AddItem "1"
    .AddItem "2"
End With

With Me
    .Controls("Label1").Visible = False
    .Controls("Label2").Visible = False
    .Controls("Label3").Visible = False
    .Controls("Label4").Visible = False
    .Controls("Label5").Visible = False
    .Controls("Label6").Visible = False
End With

With Me
    .Controls("Checkbox1").Visible = False
    .Controls("Checkbox2").Visible = False
    .Controls("Checkbox3").Visible = False
    .Controls("Checkbox4").Visible = False
    .Controls("Checkbox5").Visible = False
    .Controls("Checkbox6").Visible = False
End With
End Sub


Private Sub Vision()
Dim n As Long
With Me
    .Controls("Label1").Visible = False
    .Controls("Label2").Visible = False
    .Controls("Label3").Visible = False
    .Controls("Label4").Visible = False
    .Controls("Label5").Visible = False
    .Controls("Label6").Visible = False
End With

With Me
    .Controls("Checkbox1").Visible = False
    .Controls("Checkbox2").Visible = False
    .Controls("Checkbox3").Visible = False
    .Controls("Checkbox4").Visible = False
    .Controls("Checkbox5").Visible = False
    .Controls("Checkbox6").Visible = False
End With

For n = 1 To ComboBox1.Value
    With Me
        .Controls("Label" & n).Visible = True
        .Controls("Checkbox" & n).Visible = True
    End With
Next n
End Sub

I found ways to adjust a userform to fit the size of different monitors or to add a dragbar in the corner to adjust the size manually.

Answer 1

Here's one possible approach.

Private Sub UserForm_Activate()
    CheckSize
End Sub

Private Sub CommandButton1_Click()
    Me.lblTest.Visible = Not Me.lblTest.Visible
    CheckSize
End Sub

Private Sub CheckSize()
Dim h, w
Dim c As Control

    h = 0: w = 0
    For Each c In Me.Controls
        If c.Visible Then
            If c.Top + c.Height > h Then h = c.Top + c.Height
            If c.Left + c.Width > w Then w = c.Left + c.Width
        End If
    Next c

    If h > 0 And w > 0 Then
        With Me
            .Width = w + 40
            .Height = h + 40
        End With
    End If
End Sub