自我调整的用户表单大小 [英] 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.

在顶部始终会出现三个组合框，但在它们下方则是26个标签及其相关的五个选项按钮/复选框.这26行将全部开始隐藏，并且仅在特定条件下可见.

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.

三个组合框中的第一个将说明可能需要显示26行中的多少行.但是，只有在第二个组合框选择是"时，它们才可见.

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.

我正在使用Excel 2013.

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.

推荐答案

这是一种可能的方法.

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

这篇关于自我调整的用户表单大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！