Resizing the controls on an Access form dynamically

Question

Below is a sub I wrote to dynamically resize all the controls on a form. It utilizes the Tag property of each control. In that Tag property, I put four numbers separated by colons. So for instance, the Tag property of one particular control might be 03:10:20:10. These numbers represent Left, Top, Width, and Height (as percentages) respectively to match up to the parameters of the Move method.

In the form resize event, I then call my procedure and pass the current form:

Private Sub Form_Resize()
    RepositionControls Me
End Sub

The RepositionControls sub resides in a standard module named Utilities. (Not important, but may paint a better picture for you.)

Here is the enum used in the sub:

Public Enum ControlTag
    FromLeft = 0
    FromTop
    ControlWidth
    ControlHeight
End Enum

Here are the bones of that sub with some comments on what I'm doing:

Public Sub RepositionControls(frm As Form) 'Pass in the form as a parameter
On Error Resume Next 'This is probably out of laziness, but it prevents issues when the width of the form gets wide enough to revert to a negative number.

    Dim formDetailWidth As Long
    Dim formDetailHeight As Long
    Dim tagArray() As String

    Const HEADER_HEIGHT_PERCENTAGE = 0.1

    formDetailWidth = frm.WindowWidth
    formDetailHeight = frm.WindowHeight - frm.Section(acHeader).Height - frm.Section(acFooter).Height

    'Set the header to 10% of the forms height
    frm.Section(acHeader).Height = formDetailHeight * HEADER_HEIGHT_PERCENTAGE 

    Dim ctl As Control
    For Each ctl In frm.Section(acDetail).Controls 'Only looking at controls in the "Detail" section for now

        If ctl.Tag <> "" Then                
            tagArray = Split(ctl.Tag, ":") 'Split the "Tag" property into an array

            'Apply each number from the "Tag" property of the control to the Move method respectively
            ctl.Move formDetailWidth * (CInt(tagArray(ControlTag.FromLeft)) / 100), _
                               formDetailHeight * (CInt(tagArray(ControlTag.FromTop)) / 100), _
                               formDetailWidth * (CInt(tagArray(ControlTag.ControlWidth)) / 100), _
                               formDetailHeight * (CInt(tagArray(ControlTag.ControlHeight)) / 100)

            'Sets the font size for a label or button (needs improvement)       
            Select Case ctl.ControlType
                Case acLabel, acCommandButton
                    ctl.FontSize = (ctl.Height / 1440) * 60
            End Select

        End If

    Next


End Sub

This works well and good. When you resize the form, all the controls automatically adjust and stay proportionally the same.

Screenshot for reference (I can't post a second screenshot for comparison since I don't have 10 reputation, but this should at least give you an idea what I'm working with. The smaller version of the screenshot looks the same proportionally and has smaller text.):

Here are the Tag properties for the controls in case you want to add some random controls to a form and plug in these numbers:

03:07:15:3
03:10:20:10
03:24:94:70
13:07:10:3
25:07:15:3
25:10:25:10
40:07:10:3
52:07:15:3
52:10:25:10
67:07:10:3
79:07:15:3
79:10:18:3

Here is what I like about it:

• One sub does it all. You just pass a reference to the form and it loops through all the controls on the form (that are in the Detail section) without needing to reference controls individually.

Here are the things I don't like about it:

• The font resize part of the code is a newer addition and is just plain ugly. It takes the height of the control and finds out what percentage of an inch that is. Then it multiplies that percentage times a font size that appears to be about an inch high (60) for the Calibri font. However, that doesn't work the same with different fonts. I figure there is a straightforward way to do this, I just can't think of what it is. What I'd like is something that works the way the FMS form resizer with font adjustment works.
• It takes serious trial and error to figure out the correct numbers to put in the Tag property of each control. Ideally, I'd like to be able to design a form in design view and then have the code automatically figure out what the numbers should be in the Tag property of each control.
• Using an array is problematic if I forget to include one of the numbers in the Tag property of a control. The On Error Resume Next takes care of this for now, but it smells bad. (I used this method simply for the brevity of the Split(ctl.Tag, ":") function honestly :). However, I need to change that to something that doesn't care if I leave something out (or maybe it should error in that case). I thought about using a collection and referencing the items in that collection using a key instead of indexing into an array. That would prevent the need for the enum.
• Do any controls not have a Tag property, which might cause an error in the sub? I haven't done my due diligence on that question yet. If some controls don't have a Tag property, I would imagine I'll get an error.
• I really should handle the "form being so wide that its Width property goes negative" issue better than just reverting to the On Error Resume Next code.

Fundamentally, is it a bad idea to use the Tag property in this way? Is there a better way? I like using it because I can attach the parameters needed for the Move method directly to each control - allowing me to use the loop in the sub as I have shown. But maybe this is flawed somehow or maybe it would just be better to use another approach?

If the Tag approach is not flawed, I'm also thinking about expanding the idea of using the Tag property for other things. Maybe I can put the FontSize in there and somehow alleviate the font resizing issue I mentioned earlier. What else could I include in this Tag property that could be useful? Any ideas?

Update:

Ok, I've got part of my problem solved. When the form loads, I'm calculating the percentages for the Left, Top, Width, and Height for each control based upon where they are in the design view. I've also changed the RepositionControls sub to accommodate the fact that the numbers in the tags are now decimals, not integers. These changes mean that I don't have to figure out the Left, Top, Width, and Height numbers by trial and error. I can just add controls to the form and resize them in design view and then when I switch to form view, the controls proportionally look the same.

So, now in the Form load event, I call a new sub and pass this form:

Private Sub Form_Load()
    SaveControlPositionsToTags Me
End Sub

That sub looks like this:

Public Sub SaveControlPositionsToTags(frm As Form)

    Dim ctl As Control

    Dim ctlLeft As String
    Dim ctlTop As String
    Dim ctlWidth As String
    Dim ctlHeight As String

    For Each ctl In frm.Section(acDetail).Controls
        'Calculate the percentages and store them as strings
        ctlLeft = CStr(Round(ctl.Left / frm.Width, 2))
        ctlTop = CStr(Round(ctl.Top / frm.Section(acDetail).Height, 2))
        ctlWidth = CStr(Round(ctl.Width / frm.Width, 2))
        ctlHeight = CStr(Round(ctl.Height / frm.Section(acDetail).Height, 2))

        'Store the percentages for each control in its "Tag" property
        ctl.Tag = ctlLeft & ":" & ctlTop & ":" & ctlWidth & ":" & ctlHeight
    Next

End Sub

And the updated RepositionControls sub looks with the changes to accommodate percentages looks like this (I've also removed the code to update the font size since it was not really working):

Public Sub RepositionControls(frm As Form) 'Pass in the form as a parameter
On Error Resume Next 'This is probably out of laziness, but it prevents issues when the width of the form gets wide enough to revert to a negative number.

    Dim formDetailWidth As Long
    Dim formDetailHeight As Long
    Dim tagArray() As String

    Const HEADER_HEIGHT_PERCENTAGE = 0.1

    formDetailWidth = frm.WindowWidth
    formDetailHeight = frm.WindowHeight - frm.Section(acHeader).Height - frm.Section(acFooter).Height

    'Set the header to 10% of the forms height
    frm.Section(acHeader).Height = formDetailHeight * HEADER_HEIGHT_PERCENTAGE

    Dim ctl As Control
    For Each ctl In frm.Section(acDetail).Controls 'Only looking at controls in the "Detail" section for now

        If ctl.Tag <> "" Then
            tagArray = Split(ctl.Tag, ":") 'Split the "Tag" property into an array

            'Apply each percentage from the "Tag" property of the control to the Move method respectively
            ctl.Move formDetailWidth * (CDbl(tagArray(ControlTag.FromLeft))), _
                               formDetailHeight * (CDbl(tagArray(ControlTag.FromTop))), _
                               formDetailWidth * (CDbl(tagArray(ControlTag.ControlWidth))), _
                               formDetailHeight * (CDbl(tagArray(ControlTag.ControlHeight)))

        End If

    Next


End Sub

I would appreciate any feedback on this change, and of course any recommendations on the other items.

Answer 1

So, I fiddled enough with the code that I think I found a solution that solves almost all the problems mentioned in the question.

UPDATE

I had the percentages of the heights for the header and footer sections set by a CONST variable, but realized it would be better to set those dynamically at run time, just like all the controls. Those elements now have one number in their Tag property which represents the percentage of their height at design time in comparison to the height of the entire form.

UPDATE 2:

I added in the ability to hold down the Shift key and press + or - to make the text in all the controls on the screen bigger or smaller. The fontZoom setting should probably be retrieved from a database instead of stored in the code. This way, each user could set their desired font zoom setting and then the form would continue to use that setting the next time they opened the form. In the code below, I added the fontZoom as a parameter to the RepositionControls sub and adjusted the other code accordingly.

Here is the code behind the form:

Private fontZoom As Double

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

    'Set an unchangeable variable to the amount (10% for example) to increase or
    'decrease the font size with each change.
    Const FONT_ZOOM_PERCENT_CHANGE = 0.1

    'PURPOSE: Make the text on the form bigger if "Shift" and "+" are pressed
    'at the same time and smaller if "Shift" and "-" are pressed at the same time.
    'NOTE: Using the "Ctrl" key instead of the "Shift" key conflicts with Access's
    'default behavior of using "Ctrl -" to delete a record, so "Shift" is used instead

    'Was the "Shift" key being held down while the Key was pressed?
    Dim shiftKeyPressed As Boolean
    shiftKeyPressed = (Shift And acShiftMask) > 0

    'If so, check to see if the user pressed the "+" or the "-" button at the
    'same time as the "Shift" key. If so, then make the font bigger/smaller
    'by the percentage specificed in the FONT_ZOOM_PERCENT_CHANGE variable.
    If shiftKeyPressed Then

        Select Case KeyCode
            Case vbKeyAdd
                fontZoom = fontZoom + FONT_ZOOM_PERCENT_CHANGE
                RepositionControls Me, fontZoom
            Case vbKeySubtract
                fontZoom = fontZoom - FONT_ZOOM_PERCENT_CHANGE
                RepositionControls Me, fontZoom
        End Select

    End If

End Sub

Private Sub Form_Load()
    'Set the font zoom setting to the default of 100% (represented by a 1 below).
    'This means that the fonts will appear initially at the proportional size
    'set during design time. But they can be made smaller or larger at run time
    'by holding the "Shift" key and hitting the "+" or "-" key at the same time.
    fontZoom = 1

    'When the form loads, we need to find the relative position of each control
    'and save it in the control's "Tag" property so the resize event can use it
    SaveControlPositionsToTags Me
End Sub

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
    Debug.Print Page
    Debug.Print Count
End Sub

Private Sub Form_Resize()
    'Set the height of the header and footer before calling RepositionControls
    'since it caused problems changing their heights from inside that sub.
    'The Tag property for the header and footer is set inside the SaveControlPositionsToTags sub
    Me.Section(acHeader).Height = Me.WindowHeight * CDbl(Me.Section(acHeader).Tag)
    Me.Section(acFooter).Height = Me.WindowHeight * CDbl(Me.Section(acFooter).Tag)

    'Call the RepositionControls Sub and pass this form as a parameter
    'and the fontZoom setting which was initially set when the form loaded and then
    'changed if the user holds the "Shift" key and hits the "+" or "-" key also.
    RepositionControls Me, fontZoom
End Sub

And here is the code that can be placed in a standard module:

Public Enum ControlTag
    FromLeft = 0
    FromTop
    ControlWidth
    ControlHeight
    OriginalFontSize
    OriginalControlHeight
End Enum

Public Sub SaveControlPositionsToTags(frm As Form)
On Error Resume Next

    Dim ctl As Control

    Dim ctlLeft As String
    Dim ctlTop As String
    Dim ctlWidth As String
    Dim ctlHeight As String
    Dim ctlOriginalFontSize As String
    Dim ctlOriginalControlHeight As String

    For Each ctl In frm.Controls

        'Find the relative position of this control in design view
        'e.g.- This control is 5% from the left, 10% from the top, etc.
        'Those percentages can then be saved in the Tag property for this control
        'and used later in the form's resize event
        ctlLeft = CStr(Round(ctl.Left / frm.Width, 2))
        ctlTop = CStr(Round(ctl.Top / frm.Section(ctl.Section).Height, 2))
        ctlWidth = CStr(Round(ctl.Width / frm.Width, 2))
        ctlHeight = CStr(Round(ctl.Height / frm.Section(ctl.Section).Height, 2))

        'If this control has a FontSize property, then capture the
        'control's original font size and the control's original height from design-time
        'These will be used later to calculate what the font size should be when the form is resized
        Select Case ctl.ControlType
            Case acLabel, acCommandButton, acTextBox, acComboBox, acListBox, acTabCtl, acToggleButton
                ctlOriginalFontSize = ctl.FontSize
                ctlOriginalControlHeight = ctl.Height
        End Select

        'Add all this data to the Tag property of the current control, separated by colons
        ctl.Tag = ctlLeft & ":" & ctlTop & ":" & ctlWidth & ":" & ctlHeight & ":" & ctlOriginalFontSize & ":" & ctlOriginalControlHeight

    Next

    'Set the Tag properties for the header and the footer to their proportional height
    'in relation to the height of the whole form (header + detail + footer)
    frm.Section(acHeader).Tag = CStr(Round(frm.Section(acHeader).Height / (frm.Section(acHeader).Height + frm.Section(acDetail).Height + frm.Section(acFooter).Height), 2))
    frm.Section(acFooter).Tag = CStr(Round(frm.Section(acFooter).Height / (frm.Section(acHeader).Height + frm.Section(acDetail).Height + frm.Section(acFooter).Height), 2))

End Sub

Public Sub RepositionControls(frm As Form, fontZoom As Double)
On Error Resume Next

    Dim formDetailHeight As Long
    Dim tagArray() As String

    'Since "Form.Section(acDetail).Height" usually returns the same value (unless the detail section is tiny)
    'go ahead and calculate the detail section height ourselves and store it in a variable
    formDetailHeight = frm.WindowHeight - frm.Section(acHeader).Height - frm.Section(acFooter).Height

    Dim ctl As Control

    'Loop through all the controls on the form
    For Each ctl In frm.Controls

        'An extra (probably unncessary) check to make sure the Tag property has a value
        If ctl.Tag <> "" Then

            'Split the Tag property into an array
            tagArray = Split(ctl.Tag, ":")

            If ctl.Section = acDetail Then
                'This is the Detail section of the form so use our "formDetailHeight" variable from above
                ctl.Move frm.WindowWidth * (CDbl(tagArray(ControlTag.FromLeft))), _
                                   formDetailHeight * (CDbl(tagArray(ControlTag.FromTop))), _
                                   frm.WindowWidth * (CDbl(tagArray(ControlTag.ControlWidth))), _
                                   formDetailHeight * (CDbl(tagArray(ControlTag.ControlHeight)))
            Else
                ctl.Move frm.WindowWidth * (CDbl(tagArray(ControlTag.FromLeft))), _
                                   frm.Section(ctl.Section).Height * (CDbl(tagArray(ControlTag.FromTop))), _
                                   frm.WindowWidth * (CDbl(tagArray(ControlTag.ControlWidth))), _
                                   frm.Section(ctl.Section).Height * (CDbl(tagArray(ControlTag.ControlHeight)))
            End If

            'Now we need to change the font sizes on the controls.
            'If this control has a FontSize property, then find the ratio of
            'the current height of the control to the form-load height of the control.
            'So if form-load height was 1000 (twips) and the current height is 500 (twips)
            'then we multiply the original font size * (500/1000), or 50%.
            'Then we multiply that by the fontZoom setting in case the user wants to
            'increase or decrease the font sizes while viewing the form.
            Select Case ctl.ControlType
                Case acLabel, acCommandButton, acTextBox, acComboBox, acListBox, acTabCtl, acToggleButton
                    ctl.FontSize = Round(CDbl(tagArray(ControlTag.OriginalFontSize)) * CDbl(ctl.Height / tagArray(ControlTag.OriginalControlHeight))) * fontZoom
            End Select

        End If

    Next

End Sub