How to collapse regions in excel userform

Question

0.00/5 (No votes)

See more:

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.

Posted 13-Aug-17 9:25am

planetz

Updated 14-Aug-17 8:50am

Add a Solution

3 solutions

Solution 2

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....

VB

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.

Posted 14-Aug-17 7:27am

Pat O'Brien

Solution 3

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"

VB

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

Posted 14-Aug-17 8:49am

Pat O'Brien

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2017-08-13T21:33:00

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!