如何折叠excel userform中的区域 [英] How to collapse regions in excel userform

问题描述

大家好，



我有一个excel用户表单，需要用户输入。

现在，在表单加载时有控件隐藏，只有当用户想要添加更多字段时才会显示。这是通过单击按钮完成的。



是否有任何方法在加载时包含隐藏控件的区域被折叠，当控件变为可见时，区域会扩展？



因为，如果该区域没有崩溃，它看起来好像有人错误地留下了空白。该区域位于userform的中间区域。



任何帮助表示赞赏。



我尝试了什么：



我尝试调整滚动条，但这不起作用。空格不行。

解决方案

UserForm不提供此类功能...



您可以通过以下方式实现：

1）添加多页面控件

假设多页控件有2页。只想在用户点击按钮时显示第一个和第二个。

将其多页的样式属性设置为 fmTabStyleNone 隐藏页面。

您可以通过设置 Multipage.Value 属性来获得当前活动页面。 0 用于第一页， 1 用于第二页，依此类推。例如：

 Private Sub CmdAdditionalData_Click（）
 Me.MultiPage1.Value = 1 
 End Sub 
 
 Private Sub CmdBack_Click （）
 Me.MultiPage1.Value = 0 
 End Sub 

2）更改UserForm大小

假设您有2套控件：强制性和额外的。

强制性控件必须放在 UserForm 。额外的控制必须放在强制控制的右侧或向下，因为您只能通过增加其高度或宽度来改变控件的大小。安排在框架 s。

初始尺寸 UserForm 必须等于大小框架，其中包含强制性控制。





试试！

祝你好运！




或者，另一种更简单的方法（尽管可能被认为是暴力方法）将是调整用户表格的高度并调整位置在隐藏或可见部分下方的所有元素或控件的用户表单上....





< pre lang =vb> 私有 Sub CommandButton1_Click（）
' 隐藏或取消隐藏所有表单元素的反向可见性
Label2.Visible = 不 Label2.Visible
Label1.Visible = 不 Label1.Visible

' 调整用户表单的高度和
' 调整表单上所有其他元素的位置
如果 Label2.Visible 然后
UFTest.Height = 180 ' 大尺寸
Label3.Top = 120 ' 表格下方
Label4.Top = 140 ' 表格下方
' etc
其他
UFTest.Height = 140 ' short size
Label3.Top = 90 < span class =code-comment>' 表单上的更高
Label4.Top = 110
' etc
结束 如果
结束 Sub

我相信你可以让这段代码更灵活或者通过向表单上的所有控件添加或减去预定义的位置来高效，而不是对位置进行硬编码，但是，你得到了这个想法。




这里有一个更优雅的方式要做到这一点....



对于那些你想要隐藏或显示的控件，将隐藏放在控件的TAG属性中。 br / >


对于那些你想要向上或向下移动的控件，在TAG属性中移动。



这个假设表单开始时隐藏了一些控件，然后单击命令按钮cbShowMore。单击时，按钮上的标题切换为隐藏或返回显示

 私有  Sub  cbShowMore_Click（）
 
  Dim  ufCtrl 作为控制
  Dim  iMoveIt  As  整数 
 
 如果 cbShowMore.Caption =  显示 然后 
 iMoveIt =  30  
 cbShowMore.Caption =  隐藏 
 其他 
 iMoveIt = -30 
 cbShowMore.Caption =  显示 
 结束 如果 
 
 ' 更改高度用户表单 
 UFTest.Height = UFTest.Height + iMoveIt 
 
 对于 每个 ufCtrl 在 UFTest.Controls 
 选择 案例 UCase（ufCtrl.Tag）
 案例  隐藏 
 ufCtrl.Visible = 不 ufCtrl.Visible 
  Case    MOVE 
 ufCtrl。 Top = ufCtrl.Top + iMoveIt 
  Case   Else  
 ' 什么都不做 
 结束 选择 
 
 下一步 
 
 结束  Sub  

Hi All,

I have an excel userform that takes user input.
Now, on load of form there are controls that are hidden and only become visible if the user wants to add some more fields. This is done by a button click.

Is there any way that on load the region that contains the hidden controls is collapsed and when controls become visible, the region expands?

Because, if the region does not collapse it will look as if someone has left blank space by mistake. The region is somewhere in middle section in userform.

Any help is appreciated.

What I have tried:

I tried adjusting the scroll bars, but that did not work. The blank space is not going.

解决方案

UserForm doesn't provide such of functionality...

You can achieve that by:
1) adding Mulitpage control
Let's say Multipage control has 2 pages. You want to display only first one and the second only in case when user click on the button.
Set its Multipage's Style property to fmTabStyleNone to hide pages.
You can chance currently active page by setting Multipage.Value property. 0 is for first page, 1 for second, and so on. For example:

Private Sub CmdAdditionalData_Click()
    Me.MultiPage1.Value = 1
End Sub

Private Sub CmdBack_Click()
    Me.MultiPage1.Value = 0
End Sub

2) changing UserForm size
Let's say you have 2 sets of controls: obligatory and additional.
Obligatory controls have to be placed near of the left side of UserForm. Additional controls have to be placed on the right side of obligatory controls or down to them, because you can change the size of control only by growing its height or width. Arrange them in Frames.
Initial size of UserForm have to be equal to the size of Frame which holds obligatory controls.


Try!
Good luck!

Or, another simpler method (although may be considered a "brute force" method) would be to adjust the height of the user form and adjust the position on the user form of all of the elements or controls that are below the section being hidden or made visible....

Private Sub CommandButton1_Click()
    ' reverse visibility of all form elements to hide or un-hide
    Label2.Visible = Not Label2.Visible
    Label1.Visible = Not Label1.Visible
    
    'adjust the height of the user form and
    'adjust the position on the form for all other elements
    If Label2.Visible Then
        UFTest.Height = 180  ' large size
        Label3.Top = 120     ' lower on the form
        Label4.Top = 140     ' lower on the form
        'etc
    Else
        UFTest.Height = 140 'short size
        Label3.Top = 90     'higher on the form
        Label4.Top = 110
        'etc
    End If
End Sub

I'm sure that you could make this code more flexible or efficient by either adding or subtracting a pre-defined position to all controls on the form, rather than hard coding the position, but, you get the idea.

Here's a slightly more elegant way to do it....

For those controls that you want to hide or make visible, put "Hide" in the "TAG" property of the control.

For those controls that you want to move up or down put "move" in the TAG property.

This assumes that the form starts short with some controls hidden, then the command button "cbShowMore" is clicked. When clicked, the caption on the button is toggled to "Hide" or back to "Show"

Private Sub cbShowMore_Click()

Dim ufCtrl As Control
Dim iMoveIt As Integer

If cbShowMore.Caption = "Show" Then
    iMoveIt = 30
    cbShowMore.Caption = "Hide"
Else
    iMoveIt = -30
    cbShowMore.Caption = "Show"
End If

'change the height of the user form
UFTest.Height = UFTest.Height + iMoveIt

For Each ufCtrl In UFTest.Controls
    Select Case UCase(ufCtrl.Tag)
        Case "HIDE"
            ufCtrl.Visible = Not ufCtrl.Visible
        Case "MOVE"
            ufCtrl.Top = ufCtrl.Top + iMoveIt
        Case Else
            ' do nothing
    End Select
        
Next

End Sub

这篇关于如何折叠excel userform中的区域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！