Home
Browse Code Add-Ins Blog Language Feeds Service Stats FAQs Feedback About
Manage Your Code
Snippet: ShuffleUp (VBA)
Title: ShuffleUp Language: VBA
Description: Tabbed form with various control on each form. Where varying amounts of these controls will be programmatically hidden 'Shuffleup' goes through each tab and moves all controls up so that they are nicely positioned. Views: 544
Author: Steve Moore Date Added: 4/15/2010
Copy Code   
1Function ShuffleUp(frm As Form)
2On Error Resume Next
3Dim ctl As Control
4Dim ipos As Integer
5Dim pg As Control
6Dim bShift As Boolean
7For Each pg In frm.Controls
8        If pg.ControlType = acPage Then
9        bShift = False
10            For Each ctl In frm.Controls
11                If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox _
12                Or ctl.ControlType = acCheckBox Or ctl.ControlType = acOptionButton _
13                Or ctl.ControlType = acListBox Then
14                    If ctl.Visible = True And ctl.Parent.Name = pg.Name Then
15                    If ctl.HelpContextId = 1 Then bShift = False
16                        If bShift = False Then
17                            ipos = ctl.Top + ctl.Height
18                            bShift = True
19                        Else
20                            If ctl.ControlType = acCheckBox Or ctl.ControlType = acOptionButton Then
21                                ctl.Top = ipos + 60: ctl.Controls(0).Top = ipos + 20
22                            Else
23                                ctl.Top = ipos: ctl.Controls(0).Top = ipos
24                            End If
25                            ipos = ipos + ctl.Height
26                        End If
27                    End If
28                End If
29            Next ctl
30        End If
31Next pg
32End Function
Usage 
shufflup Me
Notes
Prerequisites:Tabbed form with various control on each form. Where varying amounts of these controls will be programmatically hidden'Shuffleup' goes through each tab and moves all controls up so that they are nicely positioned (i.e. don't have inexplicable gaps where fields have been hidden). These controls that should not move up have a '1' placed in the HelpContextID (this could be Tag if needs be). Shuffelup also relies heavily on a correct tab order, so ensure that for each control being moved upwards, it's tab property has the correct incrememnting number.