AJP Excel Information AJP Excel Information

Anchor controls and resizing userform


This is a class object that allows for automatic resizing of controls as the userform is resized.

All the code to resize and reposition the controls is handled within the class. There is no complex API code required.

The default action is to anchor controls to the top and left position of the form. This means that they maintain their position and size. You can override this setting for any of the controls. For example the Ok button in the userform above has been set to have anchors for Bottom and Right. This means that the distance from the bottom of the control and the bottom of the userform is maintained. The same for the Right edges. This results in the controls size remaining constant but it's location in anchored to the bottom right corner.
Private Sub UserForm_Initialize()

    Set m_clsAnchors = New CAnchors
    Set m_clsAnchors.Parent = Me
    ' restrict minimum size of userform
    m_clsAnchors.MinimumWidth = 45
    m_clsAnchors.MinimumHeight = 250
    With m_clsAnchors
        .Anchor("Textbox1").AnchorStyle = enumAnchorStyleLeft Or enumAnchorStyleRight
        .Anchor("cmdBrowse").AnchorStyle = enumAnchorStyleTop Or enumAnchorStyleRight
        .Anchor("labDiv1").AnchorStyle = enumAnchorStyleLeft Or enumAnchorStyleRight
        With .Anchor("frame1")
            .AnchorStyle = enumAnchorStyleLeft Or enumAnchorStyleRight Or _
                           enumAnchorStyleBottom Or enumAnchorStyleTop
            .MinimumHeight = 120
        End With
        .Anchor("listbox1").AnchorStyle = enumAnchorStyleLeft Or _
                                          enumAnchorStyleRight Or _
                                          enumAnchorStyleBottom Or _
        With .Anchor("cmdClear")
            .AnchorStyle = enumAnchorStyleBottom Or enumAnchorStyleRight
            .MinimumTop = 90
        End With
        .Anchor("labDiv2").AnchorStyle = enumAnchorStyleLeft Or _
                                         enumAnchorStyleRight Or _
        .Anchor("cmdAbout").AnchorStyle = enumAnchorStyleLeft Or enumAnchorStyleBottom
        .Anchor("checkbox1").AnchorStyle = enumAnchorStyleBottom
        .Anchor("cmdOk").AnchorStyle = enumAnchorStyleBottom Or enumAnchorStyleRight
    End With
    ' live updates whilst resizing
    CheckBox1.Value = True
    ListBox1.RowSource = "B12:B19"
End Sub
To use this in your projects you need to :-
  • include the 2 class modules.
  • the enumeration declaration in the code module.
  • in the userform itself you need to declare a private class object and set the Parent reference to the userform
All existing controls will be added when the Parent assignment is made. This includes controls added dynamically, as long as  the controls where added before the Parent assignment. For controls added to the userform afterwards you can use the Add method to add additional controls.
Example workbook
This will work in xl97 if you replace the enum with constant names instead.

Created August 2004
Last updated 5th August 2014 

Return to main page Chart Section VBA section Fun and games section Forum files Tips section Links section Book section Site information Site Search RSS feed Top of page

Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation.
andypope.info is not associated with Microsoft. Copyright ©2007-2016 Andy Pope