Dynamically Resize Form Controls With Anchors

Dynamically Resize Form Controls With Anchors

Every once in a while I work on a VB.Net project. The coolest was building an interface connecting an ArcGIS front-end to a SQL Server backend… but that’s another story. One thing I always enjoy about Visual Studio, besides the post-1900s IDE, is the forms. They have many fine features, like rich textboxes you’re allowed to use, data-connected listviews and, perhaps my favorite, dynamically resizable controls. The resizing behavior is set using left, right, top and bottom “anchors.” You set the anchors right in the Properties dialog box:

dotNet anchor property

So of course, I decided to create anchors for my VBA forms. And I think I’ve succeeded:

My Form With Anchored Controls

yoursumbuddy form

The form above has two frames, a listbox, three textboxes and two commandbuttons. Their moorings are shown in this table:

anchor settings

How Do Anchors Work?

If you use only one anchor in a pair, like only the Left anchor, then the control moves when the form is resized, maintaining the same distance between the control and the left edge of its parent container. Its size doesn’t change. If you choose both Left and Right anchors then the control grows or shrinks horizontally to fit the parent container. It’s kind of like having left-justified, right-justified or distributed text in a cell:

left right anchor demo

In this imperfect analogy, the words are the controls and the cells are the parent containers. The same concept applies to Top and Bottom anchors.

Coding the Anchors

I used to do this kind of thing piecemeal by relating the position of one control relative to its form or another control:

old style code

It works, but it’s cumbersome and requires the use of things like a WIDTH_PADDING constant, an indication that I don’t quite know what I’m doing.

It took a while to figure out the logic for handling all the form’s controls no matter where the are on the form, what types of anchors they have and whether they’re inside another control or not. At first my formulas still looked a lot like the code above, attempting to accommodate the borders around parent controls and such.

The secret I found is to just relate the anchors to the original height and width of their parent control, whether that parent is the form itself or a frame within the form. Then you can just apply the change in width or height of the parent to the position and size of the child control:

The code to do this is in a class which you instantiate and populate from the form:

Public Sub ResizeControls()
Dim i As Long

For i = LBound(m_ControlsAnchorsAndVals) To UBound(m_ControlsAnchorsAndVals)
    With m_ControlsAnchorsAndVals(i)
        If .AnchorTop And .AnchorBottom Then
            .ctl.Top = .StartingTop
            .ctl.Height = Application.WorksheetFunction.Max(0, .StartingHeight + _
                (.ctl.Parent.InsideHeight - .ParentStartingHeight))
        ElseIf .AnchorTop And Not .AnchorBottom Then
            .ctl.Top = .StartingTop
        ElseIf Not .AnchorTop And .AnchorBottom Then
            .ctl.Top = .StartingTop + (.ctl.Parent.InsideHeight - .ParentStartingHeight)
        End If
        If .AnchorLeft And .AnchorRight Then
            .ctl.Left = .StartingLeft
            .ctl.Width = Application.WorksheetFunction.Max(0, .StartingWidth + _
                (.ctl.Parent.InsideWidth - .ParentStartingWidth))
        ElseIf .AnchorLeft And Not .AnchorRight Then
            .ctl.Left = .StartingLeft
        ElseIf Not .AnchorLeft And .AnchorRight Then
            .ctl.Left = .StartingLeft + (.ctl.Parent.InsideWidth - .ParentStartingWidth)
        End If
    End With
Next i
End Sub

m_ControlsAnchorsAndVals is an array of types, one element for each control. The type specifies which anchors apply to that control, the control’s original dimensions and its parent’s original dimensions:

Private Type ControlAnchorsAndValues
    ctl As MSForms.Control
    AnchorTop As Boolean
    AnchorLeft As Boolean
    AnchorBottom As Boolean
    AnchorRight As Boolean
    StartingTop As Double
    StartingLeft As Double
    StartingHeight As Double
    StartingWidth As Double
    ParentStartingHeight As Double
    ParentStartingWidth As Double
End Type

Here’s the Userform code that fills the array of Types, instantiates the class and assigns the eight controls and their anchors:

Private Sub UserForm_Activate()
'We know how many controls we're dealing with
Dim ControlsAndAnchors(1 To 8) As ControlAndAnchors

'Chip Pearson code
MakeFormResizable Me, True
ShowMinimizeButton Me, False
ShowMaximizeButton Me, False

With ControlsAndAnchors(1)
    Set .ctl = Me.Frame1
    .AnchorTop = True
    .AnchorLeft = True
    .AnchorBottom = True
    .AnchorRight = True
End With
With ControlsAndAnchors(2)
    Set .ctl = Me.Frame2
    .AnchorTop = True
    .AnchorBottom = True
    .AnchorRight = True
End With

'... etc

With ControlsAndAnchors(8)
    Set .ctl = Me.CommandButton2
    .AnchorBottom = True
    .AnchorRight = True
End With

Set cFormResizing = New clsFormResizing
cFormResizing.Initialize Me, ControlsAndAnchors
End Sub

Add a little Chip Pearson form resizing code and you’re good to go.

Some Important or Perhaps Interesting Stuff to Know if You Try This

  1. It’s important to add the controls to the array in order of their hierarchy. If you resize a control before its parent is resized it won’t work.
  2. The WithEvents userform object seems to lack a Resize event. It does have a Layout event, which occurs whenever the form or any control on it is moved or resized. I could have worked with that, but instead I call the class’s ResizeControls subroutine from the form’s Resize event.
  3. This project makes use of Chip Pearson’s excellent API form code, which allows you to resize, and add maximize and minimize buttons to, a form.
  4. After finishing this I did a search and found that Andy Pope (of course!) did something like it ten years ago. He uses an enum, which is always fun, and has some different features, like setting a minimum control size. Unless I’m mistaken though, his code relates the change in control size or position only to the overall form, not to the control’s parent container. This can lead to oddness if you have two side-by-side frames containing controls.

Download

This download contains the code and form. It also has a copy of the table shown above that has the anchors listed for each control. I tied the table to the code so you can change the values of the anchors, run the form, and see how it behaves.

Be careful, or you might get something like this:

mixed up form

4 thoughts on “Dynamically Resize Form Controls With Anchors

  1. This is neat, Doug. It would be really cool if you could also drag on the bar between frames to adjust the size of the the thing on one side of it relative to the other. I wonder if that’s possible? I haven’t got my head around your code yet…

  2. Very cool Doug!

    This will only work on 32bit excel, right? Do you have a PtrSafe version that works on 32 or 64 bit?

    Thanks!
    -Van

    • Van, Great question! I just drug all the API modules from my Personal Macro Workbook to the downloadable workbook above, and then compiled and ran it successfully. This was on a 32-bit machine, but I use a form a few times a day on a 64-bit machine from which the modified API modules came. So, as of five minutes ago the answer should be “yes”.

      I will test it out tomorrow on a 64-bit machine, but if you could confirm that it works on yours that would be great.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>