vba 自调整用户窗体大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28100310/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Self Adjusting Userform Size
提问by Chad Portman
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.
顶部会始终显示三个组合框，但在其下方是 26 个标签及其关联的五个选项按钮/复选框。这 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.
三个组合框中的第一个将说明 26 行中可能需要可见的行数。然而，只有当第二个组合框显示是时，它们才会可见。
I am using Excel 2013.
我正在使用 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.
我找到了调整用户窗体以适应不同显示器大小或在角落添加拖动条以手动调整大小的方法。
回答by Tim Williams
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
回答by James
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
我发现问题出在显示设置上——我的显示设置为 100%，但其他人将他们的设置设置为 150%，因此当他们将其设置回 100% 时，他们可以正确查看
