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:
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.
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.
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 SubSTEP 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
Last edited:
