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

17 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.

  3. Hi There, Really amazing work.
    while debugging got an Error “user defined type not defined” in the class module at line

    Public Sub Initialize(UF As MSForms.UserForm, ControlsAndAnchors() As ControlAndAnchors)

    Can you please look into it ?

    Thanks

    • Hi Shitansu,

      If you are trying to run the attached workbook code and it’s not working then I’m not sure. However, if you’ve copied this code to your own project I’d guess you are missing the

      Public Type ControlAndAnchors

      section from the beginning of Module 1.

      • Hi Doug Glancy,

        My apologies, I should have told you that I have used the code in my project to see the behavior.

        Your code is working perfectly without any issues.

        I took your code into my project but it doesn’t work very well .

        No errors are found during debugging your code ,I took exactly what you did with all modules but when I debug getting error at
        “wsFormControlResizer” as Variable not defined

        Your code has been very helpful to me .there is some silly errors am not able to catch . Sorry to annoy you with this .

        Appreciate your time and response.

        • Hi Doug Glancy,
          I just realized that the error am getting is because of the table you have set up in your worksheet ,whereas I don’t have such table in my project .
          So, in the table wherever it is true ,Anchor will be activated for that object .
          Is there a way to anchor from all sides(Top , bottom, left and right ) by default without having that table ?

          Thanks

          • There is. The table was just designed as a demonstration (and my amusement). However the code is designed to be used as you are trying. For example, this snippet, from the post above, will anchor all four corners of a control named “Frame1.”

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

            Be sure to re-read that section above, and to look at the commented out code in the workbook’s “FancyForm” module. Let me know how it goes. We’ll get there!

  4. Thank you so much for awesome post.

    If you don’t mind I have two query.

    1. How to keep aspect ratio between width and height?

    2. How to keep multiple controls likes labels, button, textbox etc. in parent of frame (or form) center.

    • Thanks Rezaul!

      1. That would be tricky. You’d need some way to choose that mode and then avoid the endless loop that comes when you increase the horizontal at the same percentage as the vertical.

      2. To find the center of the form divide the InsideHeight and InsideWidth by 2 and then set the control’s Left and Top to those numbers minus half the control’s Width and Height.

  5. Hello Doug Glancy,
    Hope you are doing good.
    I cannot thank enough for sharing such a master piece. Its a wonderful work of highest standards.
    With the same concept, I am actually looking for a possibility of re-sizable Frame / MultiPage on a userform without affecting any other control or Userform itself during runtime (after form is loaded). my Userform is AutoFit to screen size without title bar and cannot be moved.
    Would it be possible? If Yes, can I request you guide me..?
    Thanks.

    • Thank you Gaurav! I think it’s possible, but it’s a quite different problem, especially if you want to be able to grab a corner of the frame and drag it. You might consider using keyboard shortcuts, e.g., Crtl – up arrow and Ctrl – down arrow – to increase/decrease the size by some percentage. Here’s a quick attempt:

      Private Sub MultiPage1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      If Shift = 2 Then 'ctrl key
         If KeyCode = vbKeyUp Then
            Me.MultiPage1.Height = Me.MultiPage1.Height * 1.1
            Me.MultiPage1.Width = Me.MultiPage1.Width * 1.1
         End If
         If KeyCode = vbKeyDown Then
            Me.MultiPage1.Height = Me.MultiPage1.Height / 1.1
            Me.MultiPage1.Width = Me.MultiPage1.Width / 1.1
         End If
      End If
      End Sub
  6. Hello Doug Glancy
    Thank you for the good work, really great.

    I tried to use your class in my project and I noticed strange behavior.
    My user form contains a multi-page as a parent element consisting of two frames and two list boxes.
    The frames are superordinate elements for list boxes. When I maximize the form, the multi-page size is resized and anchored in the form, but the size of the other controls does not change. On the other hand, when I try to resize the form by dragging it, it works and all the controls resize.
    Could you please tell me where the problem is? So that it also works when maximizing?

    • Hi Khaled, You found the bug! First, thanks for your kind words. Second, yes I’ve seen that issue, although I don’t use multi-pages much. Unfortunately, I’ve never tried to fix it, so don’t have an answer for you. Sorry!

Speak Your Mind

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

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