Resizable UserForm

UserForm in Excel that can be resized by the user - they can click and drag the edges or corners of the form to change its size, just like with a regular window in the operating system.

Sections:

Make Form Resizable

Resize Event

Notes

Make Form Resizable

To do this, we need to place VBA code within a regular module and also within the Activate event for the UserForm.

Module Code

The below code goes into a regular module in the VBA window. (Alt + F11 to get to the VBA window and then Insert > Module.)

Private Declare Function GetForegroundWindow Lib "User32.dll" () As Long

Private Declare Function GetWindowLong _
  Lib "User32.dll" Alias "GetWindowLongA" _
    (ByVal hWnd As Long, _
     ByVal nIndex As Long) _
  As Long

Private Declare Function SetWindowLong _
  Lib "User32.dll" Alias "SetWindowLongA" _
    (ByVal hWnd As Long, _
     ByVal nIndex As Long, _
     ByVal dwNewLong As Long) _
  As Long

Private Const WS_THICKFRAME As Long = &H40000
Private Const GWL_STYLE As Long = -16

Note: this code must go at the very top of the module before any other code!

The next section of code can be placed anywhere within the module.

Public Sub FormResizable()

Dim lStyle As Long
Dim hWnd As Long
Dim RetVal

hWnd = GetForegroundWindow

lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME
RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)

End Sub

UserForm Code

Code must also be placed within the UserForm itself in order to work.

All you need to do is call FormResizable from the Activate event for the UserForm.

FormResizable

When you put the code into the Activate event for the UserForm, it will look like this:

Private Sub UserForm_Activate()

FormResizable

End Sub

If you already have code in the Activate event, just place FormResizable at the top of this section.

To learn more about events and how to use them, view our tutoral on UserForm Events.

Resize Event

Resizing a form without chaning anything within the form window is usually useless, as such, you can put code inside of the UserForm_Resize event that will adjust the properties of the controls in the form when the user resizes it, such as increasing the size of buttons or labels within the form when a user makes the form larger.

Notes

When a form is resized by the user, using the above code, once the form has been closed and opened again, it will default back to the original size.

Make sure to download the sample file to see this example in Excel.