Create resizable VBA userform

When designing a form, its size is set. You can also set that the form should be centered and that scroll bars should be added automatically for smaller screens (see for this Center VBA userform and set scroll bars). However, at runtime, the user cannot change the dimensions of the form. This is possible in some Windows forms. An example of this is the Excel form for customizing the Ribbon (File -> Options -> Customize Ribbon). With the mouse, that form can be enlarged or reduced by dragging the bottom right corner or the form border (right or bottom).

It would be nice to be able to add such an option to VBA userforms. This article will discuss a method, based entirely on VBA, where this option can be easily added to forms.

This method uses a class module. In this class module, which is attached to the form in the UserForm_Initialize event, the necessary controls are automatically added to the form to control the resizing. This uses Frames to ensure that resize controls are always placed OnTop of any other controls present close to the form edge. This is not possible with a Label, for example.

Changing the size of the form is further controlled entirely by the class module. Therefore add a class module called clUserFormResize and put the following code in it:

'--------------------------------------------------------------------------------------------------------------------------
' Author    : Manfred van den Noort
' Copyright : © 2020 worksheetsvba.com, all rights reserved
' Date      : 2020-12-11
' Version   : 1.0
' Purpose   : Create Resizable UserForm
'--------------------------------------------------------------------------------------------------------------------------

Private WithEvents frmResizableForm As MSForms.UserForm
Private oResizableForm As Object
Private WithEvents frResizerCorner As MSForms.Frame
Private WithEvents frResizerRight As MSForms.Frame
Private WithEvents frResizerBottom As MSForms.Frame
Private sngMinHeight As Single
Private sngMinWidth  As Single
Private sngMouseX As Single
Private sngMouseY As Single

Event Resizing(ByVal X As Single, ByVal Y As Single)

Friend Property Set ResizableForm(ByRef oFrm As Object)
    Set frmResizableForm = oFrm
    Set oResizableForm = oFrm
    'set default values for MinHeight and MinWidth if they are not already set or if the values are greater than the initial dimensions
    If sngMinHeight = 0 Or sngMinHeight > oResizableForm.Height Then
        sngMinHeight = oResizableForm.Height
    End If
    If sngMinWidth = 0 Or sngMinWidth > oResizableForm.Width Then
        sngMinWidth = oResizableForm.Width
    End If
    AddResizeControls
End Property

Friend Property Let MinHeight(sngValue As Single)
    If oResizableForm Is Nothing Then
        sngMinHeight = sngValue
    ElseIf sngValue = 0 Or sngValue > oResizableForm.Height Then
        sngMinHeight = oResizableForm.Height
    Else
        sngMinHeight = sngValue
    End If
End Property

Friend Property Let MinWidth(sngValue As Single)
    If oResizableForm Is Nothing Then
        sngMinWidth = sngValue
    ElseIf sngValue = 0 Or sngValue > oResizableForm.Width Then
        sngMinWidth = oResizableForm.Width
    Else
        sngMinWidth = sngValue
    End If
End Property

Private Sub AddResizeControls()
    'frames are used to asure the resize controls are always on top of other form controls
    Set frResizerCorner = oResizableForm.Controls.Add("Forms.Frame.1")
    With frResizerCorner
        .SpecialEffect = fmSpecialEffectFlat
        .MousePointer = fmMousePointerSizeNWSE
        .ZOrder 0
        .Width = 15
        .Height = 15
    End With
    With frResizerCorner.Add("Forms.label.1")
        With .Font
            .Name = "Marlett"
            .Charset = 2
            .Size = 14
            .Bold = True
        End With
        .Caption = "o"
        .ForeColor = 6579300
        .Width = 14
        .Height = 14
        .Top = 1
        .Left = 1
        .Enabled = False
    End With
    Set frResizerRight = oResizableForm.Controls.Add("Forms.Frame.1")
    With frResizerRight
        .SpecialEffect = fmSpecialEffectFlat
        .MousePointer = fmMousePointerSizeWE
        .ZOrder 0
        .Width = 2
        .Top = 0
    End With
    Set frResizerBottom = oResizableForm.Controls.Add("Forms.Frame.1")
    With frResizerBottom
        .SpecialEffect = fmSpecialEffectFlat
        .MousePointer = fmMousePointerSizeNS
        .ZOrder 0
        .Height = 2
        .Left = 0
    End With
End Sub

Private Sub frResizerCorner_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        sngMouseX = X
        sngMouseY = Y
    End If
End Sub

Private Sub frResizerCorner_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        With oResizableForm
            If .Width + X - sngMouseX > sngMinWidth Then
                .Width = .Width + X - sngMouseX
            Else
                X = 0
                sngMouseX = 0
            End If
            If .Height + Y - sngMouseY > sngMinHeight Then
                .Height = .Height + Y - sngMouseY
            Else
                Y = 0
                sngMouseY = 0
            End If
        End With
        If X <> 0 Or Y <> 0 Then
            RaiseEvent Resizing(X - sngMouseX, Y - sngMouseY)
        End If
    End If
End Sub

Private Sub frResizerRight_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        With oResizableForm
            If .Width + X > sngMinWidth Then
                .Width = .Width + X
                RaiseEvent Resizing(X, 0)
            End If
        End With
    End If
End Sub

Private Sub frResizerBottom_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        With oResizableForm
            If .Height + Y > sngMinHeight Then
                .Height = .Height + Y
                RaiseEvent Resizing(0, Y)
            End If
        End With
    End If
End Sub

Private Sub frmResizableForm_Layout()
    With frResizerCorner
        .Left = oResizableForm.InsideWidth - .Width
        .Top = oResizableForm.InsideHeight - .Height
    End With
    With frResizerRight
        .Left = oResizableForm.InsideWidth - .Width
        .Height = frResizerCorner.Top
    End With
    With frResizerBottom
        .Top = oResizableForm.InsideHeight - .Height
        .Width = frResizerCorner.Left
    End With
End Sub

This class module must work with the form to be resizable. Therefore, an instance of this class must be created when starting the form.To do this, a variable must be added to the top of the form and 2 lines of code must be added to the UserForm_Initialize event:

Private WithEvents oFormResize As clUserFormResizer

Private Sub UserForm_Initialize()
    Set oFormResize = New clUserFormResizer
    Set oFormResize.ResizableForm = Me
End Sub

But just changing the size of the form is of course not enough. The other controls on the form must then also change size and / or move with it. However, it is almost impossible to make a general algorithm for this, so that this can also be controlled by the class module. To give an example of this: depending on the purpose of the form, the position of the buttons and the purpose of the buttons, buttons sometimes have to stay in the same place, they sometimes have to move, but it may also be desirable that they also change size. In the latter case, it may also be desirable to change the font size in addition. That is why a Resizing Event has been added to the class module, so that it can be properly set per form how the other controls should react to a change in the size of the form.

For a very simple form with only a listbox and a close button, this code is very simple and could look like this for example:

Private Sub oFormResize_Resizing(ByVal X As Single, ByVal Y As Single)
    With btnClose
        .Left = .Left + X
        .Top = .Top + Y
    End With
    With ListBox1
        .Width = .Width + X
        .Height = .Height + Y
    End With
End Sub

Depending on the computer and graphics card, it may very occasionally happen that the form does not look completely good during resizing. For example, a very small part of a button will remain visible somewhere else on the form. If this happens, Me.Repaint can be added to the Resizing event.

Optionally, the minimum height and width can also be set when configuring the clUserFormResize. By default, the initial dimensions of the form are used. Smaller dimensions can be set for this, but check carefully that this does not cause errors when making the form smaller. This is because minimum values ​​that are too small can lead to error messages, as invalid values ​​for the dimensions of form controls can be set.

There is an example file available for download that includes the above code and also includes 2 sample forms. One form is very simple in design and the other form contains more controls. In this way it is clearly visible how to configure how the controls of the form should react to a change in the size of the form..

Create resizable userform 1.0

Download