How to Dynamically keep controls centered (relative position) on an MS Access form?

Question

I'm working in Access 2013 and have a number of controls (listboxes, buttons, etc.) that I want to keep centered, as a group, on a form when the form is resized.

Anchoring won't accomplish what I'm looking for because I don't want to lock the controls to the top/bottom/left/right. I want them to stay in the center.

Simply using code like this me.mycontrol.left = myform.Width/2 on the form's resize event doesn't do what I'm looking for because it aligns the individual controls to the center. I want the group of controls to be centered.

Is there a way to do this?

EDIT: Here's an example, which may make this clearer. Suppose I have a 100 x 100 form with two buttons on it. The buttons are 20 units tall and are spaced 10 units apart. They'd have the following positions:

Button1.Top = 25 (10 unit space starts at 45) Button2.Top = 55

If the form is resized to 200 x 200, the controls would have the following positions:

Button1.Top = 75 (10 unit space starts at 95) Button2.top = 105

Ideally, I'd love to turn this into a module, where I just pass it a form and it takes the original position of each control and calculates the new position.

Edit 2:

Here's one failed attempt at it, using my real code, based onthe idea from Krish:

Private Sub Form_Resize()

Dim resizeFactor As Double

resizeFactor = Me.WindowWidth / Me.Width

Me.lstModule.Left = Me.lstModule.Left * resizeFactor
Me.ctlSubform.Left = Me.ctlSubform.Left * resizeFactor
Me.Box6.Left = Me.Box6.Left * resizeFactor

End Sub

Objectivity

Answer 1

I think the anchoring actually is the answer. You simply create a layout grid around your controls and set anchoring like this:

_____________________|___stretch down____|___________________
stretch across top___|___your controls___|stretch across top
_____________________|___stretch down____|___________________

This way your controls will stay always in the middle of the form/subform.

EDIT: Screenshots

EDIT: Added info about borders

Adding borders can be quite a pain, but to some extent, it is possible. You can do this by setting gridline colors, setting the gridline style to solid to your buttons (default is transparent) and adding some padding. In the following example, I set the first button gridline style to solid for LEFT, RIGHT and TOP and set padding 0.1" for those sides as well. If you continue with a similar fashion, your result will look like this:

Hamer

This is an intriguing answer. Since I started way back in Office 2000, I never made the move to using Layouts. I'll give it a try and follow up with you. – Objectivity

Hi DataWriter, it works great. I myself tried once to handle this by code and I wish I didn't. Thanks to your upvote my reputation got high enough to post images, so I edited my post to contatin screenshots of proof of concept. – Hamer

Wow! This works perfectly with a pair of buttons. Now to try it with a more complex form. Thanks for your help. – Objectivity

Glad to have helped. It works the same even with very complex forms with various controls and subforms, I made some really nice adaptive UI using just layout and anchoring, no VBA. Just beware that anchoring sometimes have a tendency to reset if you add/delete/merge some cells, so if the positioning suddenly stops working, just re-check your anchoring settings in all relevant cells. Follow up with me if you achieved what you wanted and if you did, please mark this answer as solution. – Hamer

I experimented with this today and it worked for me! I can't figure out how to draw a box around my controls and make them look pretty, but it's good enough for now. Thank you so much for your help! As an added bonus, I've finally learned how to use the Layout view in the process! – Objectivity

@Objectivity I edited my post answer with some info about borders – Hamer

Do you know if this is possible in MS Access 2010? I couldn't find any form items called layouts or layout grids. – Vivid

@PaulWilliams Yes, the screenshots are from 2010 version. Make sure you are in the Form Design mode and then you should see an Arrange tab on the ribbon. There you have the option to select Stacked or Tabular layout. – Hamer

Answer 2

I could use meta code:

• mark all controls in group with text tag ('Tag' property)
• iterate through all controls on form and calculate leftmost, topmost, rightmost and bottommost position for controls on form (right=left+width, etc) for all controls with a matching tag
• this is the group 'window'
• now iterate through form again, offsetting the controls by an X/Y offset calculated in relation to the group 'window'

Or, I suppose, some real code :-)

    Public Function GetControlsWindowSize(tag As String)
    Dim f As Form
    Dim c As Control
    Dim GrpLeft As Long
    Dim GrpRight As Long
    Dim GrpTop As Long
    Dim GrpBottom As Long

        For Each c In f.Controls
            If c.Properties.Item("tag") = tag Then
                If GrpLeft = 0 Or GrpLeft > c.Left Then GrpLeft = c.Left
                If GrpRight = 0 Or GrpRight < c.Left + c.Width Then GrpRight = c.Left + c.Width
                If GrpTop = 0 Or GrpTop > c.Top Then GrpTop = c.Top
                If GrpBottom = 0 Or GrpBottom < c.Top + c.Height Then GrpBottom = c.Top + c.Height
            End If
        Next
    End Function

Goidelic

I like this, and tried it myself, but I had trouble coming up with a formula for the resize factor. Do you have an idea? – Objectivity

Just re-read the OP. From what you're saying, the control sizes would remain the same and the left (top follows same pattern) would be: mycontrol.left = myform.Width/2 - controlwindow.width/2 + (mycontrol.left - controlwindow.left) – Goidelic

.. added some code for calculating window size. after that, iterate again and apply the scaling factor – Goidelic

Answer 3

I realize this is many years late, but I have an alternate VBA solution for you. I wrote this out after trying many other people's code, but not being satisfied with how they worked.

What I wanted was to have ALL the controls to be centered within the form (pop-up form btw). However I didn't want them all mashed together right in the center, I wanted them to be "Grouped", and have the group of controls centered in the form rather than each individual control centered.

Another condition that I required was it needed to have the controls centered when the window was maximized, or in a smaller window. The controls needed to dynamically center in the form as the user adjusts the form size.

I made a sub procedure that needs to be called in the Private Sub Form_Resize() event and the Private Sub Form_Current() event. I'm using Access 2010.

Private Sub Form_Resize()
'Should run every time the form is resized
    Call CenterControls
End Sub

And

Private Sub Form_Current()
  'Will run when the form is completing its initialization
   DoCmd.Maximize   'Maximizes the form when first initialized. Omit if required.
   Call CenterControls
End Sub

Then this is where the magic happens.

Sub CenterControls()

'Find the control that has the farthest left position, and the one with the farthest right position.
'That will give us the total distance of our buttons. We will then have to compare that to the width of the form, and move our controls accordingly.
Dim Ctrl As Control
Dim ClosestLeft As Integer
Dim FurthestRight As Integer
Dim FurthestRightWidth As Integer
Dim GrandTotalWidth As Integer
Dim AmountToMove As Integer
Dim TypicalPosition As Integer


'Finds the furthest left position of all of our controls on the form.
For Each Ctrl In Me
    If ClosestLeft > Ctrl.Left Or ClosestLeft = 0 Then
        ClosestLeft = Ctrl.Left
    End If

'Finds the furthest right control. Also takes the width of that furthest right control (necessary for the calculation)
    If FurthestRight < Ctrl.Left Or FurthestRight = 0 Then
        FurthestRight = Ctrl.Left
        FurthestRightWidth = Ctrl.Width
    End If
Next

'Now we find the grand total width of all of our controls. Furthest Right - Furthest Left + FurthestRightWidth
GrandTotalWidth = FurthestRight - ClosestLeft + FurthestRightWidth

'Finds the typical position of where the left side of the group of controls should sit on the form.
TypicalPosition = (Me.InsideWidth / 2) - (GrandTotalWidth / 2)

'Figures out the amount we'd have to move the group of controls to get them to sit in their typical position.
AmountToMove = TypicalPosition - ClosestLeft

For Each Ctrl In Me
    If Not ClosestLeft + AmountToMove <= 0 Then
        Ctrl.Left = Ctrl.Left + AmountToMove
    End If
Next


End Sub

Now when you run your pop-up form, all the controls should be centered to the form as a group.

Bridgeman

Answer 4

AFAIK there aren't any layout "containers" in MS Access (except the tabular/stacked view). Your best shot would be to add a string to your components.Tag property and loop through the components that have to be realigned

something like this:

        Dim iCtl As control
        For Each iCtl In Me.Form
            If iCtl.Tag = "resize" Then
                On Error Resume Next
                Debug.Print "control resizing: " & iCtl.name
                iCtl.width = iCtl.width * resizeFactor
                iCtl.Height = iCtl.Height * resizeFactor
                'left top whatever you want to re align
            End If
        Next iCtl

Sidewheel

I clarified my question a bit after reading your answer. It looks like this approach could work. Do you know how I'd calculate the resizeFactor? – Objectivity

Answer 5

The answer submitted by Thebaby is an elegant solution - and I've tried several. I'm running Access 16 on Windows 10.

I recommend putting the Sub CenterControls() in a module as a Public Function and call it from the Form_Resize() and Form_Current() of each form where you use it.

As a Public Function you will need to replace all the references to Me, which can only be called inside a form, with screen.activeform and the relevant property.

For ex: For Each Ctrl In Me For Each Ctrl In screen.activeform.controls

Or

TypicalPosition = (Me.InsideWidth / 2) - (GrandTotalWidth / 2) needs to be updated to: TypicalPosition = (screen.activeform.InsideWidth / 2) - (GrandTotalWidth / 2)

Tiana

I used the above code on access 365 and give me an error on the last line Ctrl.Left = Ctrl.Left + AmountToMove – Weirick

Answer 6

Expanding on Thebaby's elegant approach, donohealy's addition, and adding in Ben McIntyre's tagging, I was able to come up with this module function.

Main Differences:

1. Fixes a minor math issues that doesn't account for wide objects
2. Now controls Y axis
3. Tagging allows for fine control over what moves (tab controls broke in the original)
4. Fixes the issue that the user commenting on donohealy's post mentions

Drawbacks:

1. Still not super smooth
2. Listboxes still are weird

  
    Public Function CenterControls(obj As Form, Centertag As String, Middletag As String)

    'Find the control that has the farthest left position, and the one with the farthest right position.
    'That will give us the total distance of our buttons. We will then have to compare that to the width of the form, and move our controls accordingly.
    Dim Ctrl As Control
    Dim ClosestLeft As Integer
    Dim FurthestRight As Integer
    Dim GrandTotalWidth As Integer
    Dim AmountToMoveLR As Integer
    Dim TypicalPositionLR As Integer

    Dim ClosestTop As Integer
    Dim FurthestDown As Integer
    Dim GrandTotalHeight As Integer
    Dim AmountToMoveTB As Integer
    Dim TypicalPositionTB As Integer

    Dim StopLR As Boolean
    Dim StopTB As Boolean


    'Finds the furthest left position of all of our controls on the form.
    For Each Ctrl In obj
        If (Ctrl.Properties.Item("tag") = Centertag) Then
            If ClosestLeft > Ctrl.Left Or ClosestLeft = 0 Then
                ClosestLeft = Ctrl.Left
            End If
    
            'Finds the furthest right control. Also takes the width of that furthest right control (necessary for the calculation)
            If FurthestRight  Ctrl.Top Or ClosestTop = 0 Then
                ClosestTop = Ctrl.Top
            End If
    
            'Finds the furthest right control. Also takes the width of that furthest right control (necessary for the calculation)
            If FurthestDown = obj.InsideWidth Then StopLR = True
    If (ClosestLeft + AmountToMoveLR) = obj.InsideHeight) Then StopTB = True
    If (ClosestTop + AmountToMoveTB  

Penrod