[TIP] Expand/Collapse a Subform or a Section of your form/page (1 Viewer)

Zak14

Registered User.
Local time
Today, 16:59
Joined
Jun 27, 2014
Messages
166
This is a neat way to expand/collapse a subform or a section of your page (or any page control for that matter), whilst moving everything below it down/up respectively.

NOTE: Example/Preview database is attached to this post (below).
or Click Here


STEP 1)
Open your form in design view.

STEP 2)
Create the button/label that you want to click for everything to happen.
My example will be called cmdShutter.

STEP 3)
Create a label/button that says "-".
(You can make this the click button if you want by referencing this instead of cmdShutter OR you can have as many expand/collapse buttons as you want by copying the same code of cmdShutter into those buttons).
This label is for deciding whether to expand or collapse - if you don't want users seeing this label, you can set its visible property to "no".
My example of this label/button will be called lblIndicator

STEP 4)
Select every item in the form (can be 1 or can be many) that you want to expand/collapse (show/hide).
Then, open the property sheet and enter/add a piece of text (for use later) into the 'Tag' property (which is under the 'Other' tab).
Our example tag will be "ExpandCollapseSection1" [without the quotes]

STEP 5)
Select all the items in the form that you want affected (moved down/up) as a result of expanding/collapsing.
Again, enter/add a piece of relevant text (for use later) into the tag property.
Our example will be "ItemBelowECSection1"

STEP 6)
If you're expanding/collapsing multiple items (controls) in your form (like a section of your page), rather than just 1 control (like a subform), you need a box frame (or any control) that is the height of everything you want to expand/collapse, such as a rectangle frame around your expand/collaspe section - think like, this is so that you can tell the code to show/hide everything within the box frame.
If you don't want a frame to show around the expand/collapse section, just set it's visible property to "no".
My example of this frame is called frameECSection1.
If, however, you're expanding/collapsing just 1 item, whenever this reference "frameECSection1" comes up, you should be referring to that 1 item.

STEP 7)
Select cmdShutter and in the 'Event' tab of the property sheet, click the "..." next to the 'On Click' property.
Select the 'Code builder' if it asks.

STEP 8)
Visual Basic should now open and you should be presented with the following code:
Code:
Private Sub [COLOR="red"]cmdShutter[/COLOR]_Click()

End Sub
Between these two lines is where all the action will take place for your button (again, in our case, called 'cmdShutter')

STEP 9)
Put the following code in between the two lines. Remember to change the names (in red) to your own control/tag names.
Code:
    Dim ctl As Control      [COLOR="blue"]'// This sets the variable 'ctl' for use below[/COLOR]

    For Each ctl In Me.Controls
    [COLOR="blue"]'// This 'For' statement repeats the following code (until its corresponding 'Next') for each
    '   control in the current form individually.[/COLOR]
    
        [COLOR="blue"]'// SECTION 1 - EXPANDING/COLLAPSING THE SECTION/SUBFORM
        '[/COLOR]
        If InStr(1, ctl.Tag, "[COLOR="Red"]ExpandCollapseSection1[/COLOR]") <> 0 Then
        [COLOR="blue"]'// This checks to see if the control has an "ExpandCollapseSection1" anywhere within
        '   its tag property.
        '// For every control that has, the following (until its corresponding 'End If') happens.[/COLOR]
        
            If Me.ActiveControl Is ctl Then
            [COLOR="blue"]'// Before we get to expanding/collapsing, any 'to be collapsed' control must be
            '   inactive, since you can't hide an active control.
            '// This checks to see if any control in the collapse section is active.
            '// If it is, the following happens.[/COLOR]
                Me.[COLOR="red"]txtAddress[/COLOR].SetFocus
                [COLOR="blue"]'// The next control after the expand/collapse section of my form is made active.
                '// In this case, it's txtAddress. Set it to anything you want.[/COLOR]
            End If
            
            If [COLOR="red"]lblIndicator[/COLOR].Caption = "-" Then
                ctl.Visible = False
            Else
                ctl.Visible = True
            End If
            [COLOR="blue"]'// This 'If' statement checks whether the indicator is -.
            '// If it is a -, then control(s) (subform/section) hides.
            '// However, if it is not a - (but a +), the control(s) is shown.[/COLOR]
            
        End If      [COLOR="blue"]'// Ends the 'If InStr' statement[/COLOR]
        
        [COLOR="blue"]'// SECTION 2 - MOVING ITEMS BELOW DOWN/UP
        '[/COLOR]
        If InStr(1, ctl.Tag, "[COLOR="red"]ItemBelowECSection1[/COLOR]") <> 0 Then
        [COLOR="blue"]'// This checks to see if the control has an "ItemBelowECSection1" anywhere within
        '   its tag property. If it does, the following happens for it.[/COLOR]
        
            If [COLOR="red"]lblIndicator.Caption[/COLOR] = "-" Then
                ctl.Top = ctl.Top - [COLOR="red"]frameECSection1[/COLOR].Height
            Else
                ctl.Top = ctl.Top + [COLOR="red"]frameECSection1[/COLOR].Height
            End If
            [COLOR="blue"]'// If the Indicator is -, the control moves up the height of the section's frame
            '   or the subform. Otherwise, it moves down the height of the frame/subform.[/COLOR]
            
        End If
        
    Next        [COLOR="blue"]'// Ends the 'For' Statement[/COLOR]
    
    [COLOR="blue"]'
    '// SECTION 3 - INDICATOR CHANGE
    '[/COLOR]
    If [COLOR="red"]lblIndicator[/COLOR].Caption = "-" Then
        [COLOR="red"]lblIndicator[/COLOR].Caption = "+"
    Else
        [COLOR="red"]lblIndicator[/COLOR].Caption = "-"
    End If
    [COLOR="blue"]'// If the indicator is a -, it's changed to a +; otherwise it's changed to a -.[/COLOR]
    
    Set ctl = Nothing      '//  Resets the variable 'ctl'
[COLOR="Blue"]'
'
'// You can simply delete these ' comments if you want after you're done and it's working.[/COLOR]

NOTE:
The above keeps the expand/collapse subform/section open when the form is opened.
If you'd like it collapsed as default, you can simply copy the above code into the load event of the form.

(To do this, select 'Form' in the drop-down box on the top of the property sheet.
Go to the 'Event' tab and open the code builder for the 'On Load' property.)

Copying the above code into the form's 'on load' event makes the section collapse as the form loads.

Below is a simplified version, just for the 'on load' event.
Code:
    Dim ctl As Control
    
    For Each ctl In Me.Controls
    
        If InStr(1, ctl.Tag, "[COLOR="red"]ExpandCollapseSection1[/COLOR]") Then
            ctl.Visible = False
        End If

        If InStr(1, ctl.Tag, "[COLOR="red"]ItemBelowECSection1[/COLOR]") Then
                ctl.Top = ctl.Top - [COLOR="red"]frameECSection1[/COLOR].Height
        End If
        
    Next
    
    [COLOR="red"]lblIndicator[/COLOR].Caption = "+"
    
    Set ctl = Nothing
 

Attachments

  • Expand-Collapse System.accdb
    776 KB · Views: 3,214
Last edited:

eq2home

New member
Local time
Today, 12:59
Joined
May 25, 2016
Messages
3
You are a Guru. Thanks very much for this very helpful and well documented explanation and example.:)

FIVE StarS!
 
Last edited:

eq2home

New member
Local time
Today, 12:59
Joined
May 25, 2016
Messages
3
A quick question...I have a Tab Control with a subform on it. The subform container moves but not the individual controls on the subform. Do you happen to have a trick to make the form controls follow the form container move? Thanks Jim
 

araffa040872

New member
Local time
Today, 09:59
Joined
Mar 23, 2019
Messages
1
Good day,

I know this is an old topic but I wanted to make a contribution especially when I found something that works.

These 2 lines of code will basically expand or collapse all. If you have nested subforms, you simply have to add 1 line of code per subform. Just make sure the syntax is correct.

In my example, I made 2 buttons (cmdCollapse and cmdExpand)



Private Sub cmdCollapse_Click()

Me.1stSubform.Form.SubdatasheetExpanded = False

Me.1stSubform!2ndSubform.Form.SubdatasheetExpanded = False

End Sub

Private Sub cmdExpand_Click()

Me.1stSubform.Form.SubdatasheetExpanded = True

Me.1stSubform!2ndSubform.Form.SubdatasheetExpanded = True

End Sub
 

isladogs

MVP / VIP
Local time
Today, 16:59
Joined
Jan 14, 2017
Messages
18,186
Hi araffa

Welcome to the forum and thank you for your contribution
In fact, you could combine your code into one button:

Code:
Private Sub cmdCollapseExpand_Click()

If cmdCollapseExpand.Caption = "Expand" Then
   Me.1stSubform.Form.SubdatasheetExpanded = True 
   Me.1stSubform!2ndSubform.Form.SubdatasheetExpanded = True
    cmdCollapseExpand.Caption = "Collapse"
Else
    Me.1stSubform.Form.SubdatasheetExpanded =False
    Me.1stSubform!2ndSubform.Form.SubdatasheetExpanded = False
    cmdCollapseExpand.Caption = "Expand"
End If

End Sub

Your code will of course only work with subdatasheets.

The attached app was done in answer to two separate forum questions about altering subform size using code
It shows how you can collapse expand a subform completel and move other controls up/down accordingly
It also has a separate form showing how the width of the subform can be reduced/increased

BTW:
You may be aware that subdatasheets have to be loaded whether or not they are expanded. This causes a performance hit on the application.
For that reason, subdatasheets are normally best avoided
 

Attachments

  • SubformShrink&Grow_v3A.zip
    65.5 KB · Views: 782

Happytobealive

New member
Local time
Today, 12:59
Joined
Oct 8, 2021
Messages
21
Hi and thank you so much for this wonderful explanation, i was wondering if there is a way to do multiple subforms and each subform should have a button to expand and when they are collapsed all the buttons should align to each other? also here you a referencing a text box for the active controls, how can i reference the subform (it's not working as me.subform). i might be asking simple questions but i'm new. thank you
 

isladogs

MVP / VIP
Local time
Today, 16:59
Joined
Jan 14, 2017
Messages
18,186
Hi & welcome to the forum
It isn't clear which of us your question is directed at.
 

Happytobealive

New member
Local time
Today, 12:59
Joined
Oct 8, 2021
Messages
21
Thank you, i was asking on the original post. however anyone who can help would be a huge help for me.
 

isladogs

MVP / VIP
Local time
Today, 16:59
Joined
Jan 14, 2017
Messages
18,186
Did you look at my example app in post #5? In my opinion, the code is both better than the original and is more concise.

Although that example only has one subform, the same approach could easily be used with multiple subforms.
If you know that all subforms would always be collapsed or expanded at the same time, just use a single button for this.
Otherwise if you want the subforms to be managed independently, use a separate button to manage each subform
 

Happytobealive

New member
Local time
Today, 12:59
Joined
Oct 8, 2021
Messages
21
Yes i looked at it many time and i couldn't figure out how to do it, i have a continuous form not a datasheet, also the subforms are around 7/8 different ones that will be expanded independently, that's why i need to know how they can align when collapse, and move down when expanded..
 

isladogs

MVP / VIP
Local time
Today, 16:59
Joined
Jan 14, 2017
Messages
18,186
OK so what have you tried?

You need a separate button for each subform and then adjust the subform position relative to the bottom of the previous subform.
You can get that in code using the top position of that subform together with its height
If I have time, i'll try to do another example with two or three subforms.
 

Happytobealive

New member
Local time
Today, 12:59
Joined
Oct 8, 2021
Messages
21
OK so what have you tried?

You need a separate button for each subform and then adjust the subform position relative to the bottom of the previous subform.
You can get that in code using the top position of that subform together with its height
If I have time, i'll try to do another example with two or three subforms.
Thank you, that will be a great help. looking forward.
 

isladogs

MVP / VIP
Local time
Today, 16:59
Joined
Jan 14, 2017
Messages
18,186
Attached is an example with 4 subforms, each of which can be made visible/hidden.
The subform heights are automatically expanded when visible & shrunk to zero when hidden.
The overall form height is automatically expanded or shrunk in line with changes to subforms.

The idea can easily be modified for any number of subforms providing that the maximum section height allowed in a form design isn't exceeded. For info that limit is 55.87 cm (approx 22 inches)
 

Attachments

  • SubformShrink&Grow_v4.zip
    60.5 KB · Views: 501

Happytobealive

New member
Local time
Today, 12:59
Joined
Oct 8, 2021
Messages
21
Attached is an example with 4 subforms, each of which can be made visible/hidden.
The subform heights are automatically expanded when visible & shrunk to zero when hidden.
The overall form height is automatically expanded or shrunk in line with changes to subforms.

The idea can easily be modified for any number of subforms providing that the maximum section height allowed in a form design isn't exceeded. For info that limit is 55.87 cm (approx 22 inches)
Thank you Isla,
that's a huge help, also is there anyway to add that when the 2nd btn is pressed the 1st one collapse?
 

isladogs

MVP / VIP
Local time
Today, 16:59
Joined
Jan 14, 2017
Messages
18,186
Are you saying you only ever want one item open at a time?
If so, this isn't the easiest way to do it.

Instead I suggest you have a combo with a list of subforms to choose from
Below that have a single 'master' subform containing no data and no controls. It just acts as a 'container'
When the combo selection is made just set the master subform source object to the subform selected with the combo.

Does that make sense?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:59
Joined
May 7, 2009
Messages
19,169
sample, sample.
 

Attachments

  • xp style menu.accdb
    752 KB · Views: 471

Happytobealive

New member
Local time
Today, 12:59
Joined
Oct 8, 2021
Messages
21
Are you saying you only ever want one item open at a time?
If so, this isn't the easiest way to do it.

Instead I suggest you have a combo with a list of subforms to choose from
Below that have a single 'master' subform containing no data and no controls. It just acts as a 'container'
When the combo selection is made just set the master subform source object to the subform selected with the combo.

Does that make sense?
thank you, i just added the hide shrink to the buttons. also is there a way to make it adjust to a continuous form?
 

Happytobealive

New member
Local time
Today, 12:59
Joined
Oct 8, 2021
Messages
21
sample, sample.
thank you, it looks great however i'm new to vb so i can't understand it so well, i need a better tutorial on the code so i would be able to implement it for my DB. (beside the coding, i'm not sure why there are so many labels?).
 

Users who are viewing this thread

Top Bottom