CODEWITHSUNDEEP
excelvbaresizeuserformexcel-2013
Chad Portman
Chad Portman

Reputation: 1216

Self Adjusting Userform Size

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.

Upvotes: 2

Views: 24167

Answers (2)

James
James

Reputation: 1

I found that the issue lied with display settings - I have my display set to 100% but others had theirs set to 150% so when they set it back to 100% they could view it correctly

Upvotes: -1

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 4

Related Questions