Resize a UserForm with VBA or Windows API

When creating a VBA UserForm, we generally set it to a specific size.  Most other forms and windows within the Excel and Windows environments do not have a fixed sized; they can be resized by the user.  With a bit of coding magic, we can achieve a similar resizing effect for our VBA UserForms.  This post will show you how.

There are two solutions presented below, a Windows API method and a VBA only method.  Of the two, the Windows API solution has a smoother, more integrated feel for the user, but it will only work on Windows.  If your code is expected to work on Windows and Mac, then using the VBA solution is the better option.

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0178 Resize VBA UserForm.zip

Windows API Solution

Windows API codes make use of special functions which are not part of Excel or VBA, but part of the main Windows application.  The topic of Windows API codes is too big to discuss here, but by following the instructions below you can still get the code working, even if you don’t have a full understanding of why it works.

Remember, Windows API codes will only work on Windows.

How does it work?

A brief overview of how the process works will help with understanding what the code below does.

  1. Change the Windows setting to enable the UserForm to be resized.
  2. On the UserForm use the Resize event to capture when the form is resized.
  3. The objects’ size or position changes after each resize event.

Setting up the Windows API code

Copy the following code into a new standard module.  It must be included at the top of the module before any functions or subprocedures, but below the Option Explicit statement (if there is one).

Public Const GWL_STYLE = -16
Public Const WS_CAPTION = &HC00000
Public Const WS_THICKFRAME = &H40000

#If VBA7 Then
    Public Declare PtrSafe Function GetWindowLong _
        Lib "user32" Alias "GetWindowLongA" ( _
        ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Public Declare PtrSafe Function SetWindowLong _
        Lib "user32" Alias "SetWindowLongA" ( _
        ByVal hWnd As Long, ByVal nIndex As Long, _
        ByVal dwNewLong As Long) As Long
    Public Declare PtrSafe Function DrawMenuBar _
        Lib "user32" (ByVal hWnd As Long) As Long
    Public Declare PtrSafe Function FindWindowA _
        Lib "user32" (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
#Else
    Public Declare Function GetWindowLong _
        Lib "user32" Alias "GetWindowLongA" ( _
        ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Public Declare Function SetWindowLong _
        Lib "user32" Alias "SetWindowLongA" ( _
        ByVal hWnd As Long, ByVal nIndex As Long, _
        ByVal dwNewLong As Long) As Long
    Public Declare Function DrawMenuBar _
        Lib "user32" (ByVal hWnd As Long) As Long
    Public Declare Function FindWindowA _
        Lib "user32" (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
#End If

This following code must be included within the same module as the code above but does not need to be directly below it.

Sub ResizeWindowSettings(frm As Object, show As Boolean)

Dim windowStyle As Long
Dim windowHandle As Long

'Get the references to window and style position within the Windows memory
windowHandle = FindWindowA(vbNullString, frm.Caption)
windowStyle = GetWindowLong(windowHandle, GWL_STYLE)

'Determine the style to apply based
If show = False Then
    windowStyle = windowStyle And (Not WS_THICKFRAME)
Else
    windowStyle = windowStyle + (WS_THICKFRAME)
End If

'Apply the new style
SetWindowLong windowHandle, GWL_STYLE, windowStyle

'Recreate the UserForm window with the new style 
DrawMenuBar windowHandle

End Sub

The two code segments above create a reusable procedure which we can use to toggle the UserForm’s resize setting on or off.   Anytime we want to turn on resizing  for a UserForm, use the following:

Call ResizeWindowSettings(myUserForm, True)

To turn off resizing, use the following

Call ResizeWindowSettings(myUserForm, False)

Just replace myUserForm with the name of your form, or use Me if within the UserForm’s code module.

Setting up the UserForm

To illustrate the process,  I’ve created a UserForm which looks like this:

Windows API Resize

There are just two elements:

  • A ListBox called lstListBox
  • A Button called cmdClose

Both of these elements should change when then UserForm resizes.  lstListBox should change in size, but not in position while the cmdClose will change in position but not in size.  To enable this to happen we need to know the position of these objects from the bottom and right of the UserForm.  Provided we keep the same distance from the bottom and right it will appear that these items are moving in sync with the UserForm.

The code below must be included within the UserForm’s code module to function correctly.

To capture the initial bottom and right positions of the ListBox and Button, we set up some Private variables to hold the values.

Private lstListBoxBottom As Double
Private lstListBoxRight As Double
Private cmdCloseBottom As Double
Private cmdCloseRight As Double

Now let’s set up what happens when the UserForm initializes.

Firstly, resizing is enabled by calling the code created in the Windows API section above.  Secondly, we store the position of the objects in the private variables created above.

Private Sub UserForm_Initialize()

'Call the Window API to enable resizing
Call ResizeWindowSettings(Me, True)

'Get the bottom right anchor position of the objects to be resized
lstListBoxBottom = Me.Height - lstListBox.Top - lstListBox.Height
lstListBoxRight = Me.Width - lstListBox.Left - lstListBox.Width
cmdCloseBottom = Me.Height - cmdClose.Top - cmdClose.Height
cmdCloseRight = Me.Width - cmdClose.Left - cmdClose.Width

End Sub

Next, using the UserForm’s resize event, we change the size/position of ListBox and the Button:

  • lstListBox changes height and width
  • cmdCloses changes top and left position
Private Sub UserForm_Resize()

On Error Resume Next

'Set the new position of the objects
lstListBox.Height = Me.Height - lstListBoxBottom - lstListBox.Top
lstListBox.Width = Me.Width - lstListBoxRight - lstListBox.Left
cmdClose.Top = Me.Height - cmdCloseBottom - cmdClose.Height
cmdClose.Left = Me.Width - cmdCloseRight - cmdClose.Width

On Error GoTo 0

End Sub

That is it.  You’ve now got the code you need.  So fire up the UserForm and start resizing.

VBA Resize - icon

When the mouse hovers over the edge of the UserForm, the icon changes, just click and drag.  With this method, resizing can happen on any side of the UserForm.

VBA only solution

The VBA solution makes use of mouse events to trigger when to change the height and width of the UserForm.  Personally, I think it’s not as smooth as the Window API solution, but it is much easier to understand and can be used on a Mac too.

How does it work?

The VBA solution uses a different method of application to the Window API solution.

  1. The UserForm contains an object which when clicked records the position of the mouse.
  2. As the mouse moves, the UserForm and it’s objects are repositioned or resized based on the new mouse position.
  3. When the mouse button is released, movement ceases to adjust the size.

Setting up the UserForm

To illustrate the process, I have created anotherUserForm; it looks like this:

VBA Resize

There are just three elements:

  • ListBox called lstListBox
  • Button called cmdClose
  • Label called lblResizer

The lblResizer is a label which includes the “y” character from the Wingdings 3 font set.  This displays as a small triangle in the bottom right corner to show the user where to click to resize the window.   The Color and MousePointer properties are set as follows:

lblResizer Settings

All of these elements, along with the UserForm itself will need to change when the mouse is clicked and moved whilst over the lblResizer object.

All the code must be contained within the UserForm’s code module.

We will set up some Private variables to hold the mouse position and click status, along with the minimum allowed window size.

Private resizeEnabled As Boolean
Private mouseX As Double
Private mouseY As Double
Private minWidth As Double
Private minHeight As Double

When initializing the UserForm, the code below will place the lblResizer into the bottom right corner, and set the minimum permitted window size.

Private Sub UserForm_Initialize()

'Position the resize icon
lblResizer.Left = Me.InsideWidth - lblResizer.Width
lblResizer.Top = Me.InsideHeight - lblResizer.Height
minHeight = 125
minWidth = 125

End Sub

The following code triggers when the mouse clicks on the lblResizer icon.  The code records that the icon has been clicked and the position of the mouse at that moment.

Private Sub lblResizer_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
    ByVal X As Single, ByVal Y As Single)

'The user clicked on the lblResizer
resizeEnabled = True

'Capture the mouse position on click
mouseX = X
mouseY = Y

End Sub

The following code triggers when the mouse moves while over the lblResizer.

Firstly, it will check that the window is larger than the minimum permitted size, and that mouse has been clicked.  If both of these are True, the UserForm and the objects are re-positioned or resized based on the size of the mouse movement.

Private Sub lblResizer_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _
    ByVal X As Single, ByVal Y As Single)

'Check if the UserForm is not resized too small
Dim allowResize As Boolean

allowResize = True

If Me.Width + X - mouseX < minWidth Then allowResize = False
If Me.Height + Y - mouseY < minHeight Then allowResize = False

'Check if the mouse clicked on the lblResizer and above minimum size
If resizeEnabled = True And allowResize = True Then

    'Resize/move objects based on mouse movement since click

    'Resize the UserForm
    Me.Width = Me.Width + X - mouseX
    Me.Height = Me.Height + Y - mouseY

    'Resize the ListBox
    lstListBox.Width = lstListBox.Width + X - mouseX
    lstListBox.Height = lstListBox.Height + Y - mouseY

    'Move the Close Button
    cmdClose.Left = cmdClose.Left + X - mouseX
    cmdClose.Top = cmdClose.Top + Y - mouseY

    'Move the Resizer icon
    lblResizer.Left = Me.InsideWidth - lblResizer.Width
    lblResizer.Top = Me.InsideHeight - lblResizer.Height

End If

End Sub

The following code triggers when the mouse button is released; the mouse movement ceases to resize the UserForm.

Private Sub lblResizer_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
    ByVal X As Single, ByVal Y As Single)

'The user un-clicked on the lblResizer
resizeEnabled = False

End Sub

That is it; we’re good to go. Open up the UserForm and start resizing.

VBA Resize - icon

In this example, the UserForm only resizes when the mouse clicks on the icon in the bottom right.  We could add the ability to expand from the right, bottom, or left by using objects positioned just inside the edges of the UserForm.


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

16 thoughts on “Resize a UserForm with VBA or Windows API”

  1. Good Evening,

    thank you very much for the explanation.
    I thought about adding a min/max limitation of the resizing by using the WindowProc API-function together with WM_GETMINMAXINFO.

    Unfortunately, I don’t get any working results.

    Do you have also any code snippet or an example of how to lmit the sizing?

    Best regards
    Stephan

    Reply
    • Hi Stephan,

      Do you need to use a Windows API for this? You could use something like the following in the UserForm_Resize() macro. It will restrict the window to 200 pixels wide.

      If Me.Width > 200 Then
          Me.Width = 200
          Exit Sub
      End If
      
      Reply
      • Good Morning

        thank you for the quick reply.
        I’d already applied such a macro, but it’s like with the VBA solution for resizing the form. It’s not as smooth as the Windows API solution, which is the reason why I tried to make use of the windows API.
        Unfortunately, the WindowProc function needs a pointer to the WM_GETMINMAXINFO parameter lParam. There the problems begin.

        Reply
  2. Good Afternoon,

    I am using e ‘VBA’ Solution (Excel 2010Pro, Windows 10) and have run into a very odd issue.

    The WIDTH of the form__ cannot__ be decreased to less than 100 units whereas the HEIGHT can.

    Curious – would welcome your advice

    Many thanks

    Michael Message

    Reply
    • Hi Michael
      I found your finding curious so I checked on my end.

      I added at the beginning of userform_initialize, Me.Caption = “l” so that a caption may not force a wider width.

      I added
      Private Sub UserForm_Terminate()
      Debug.Print Me.Width
      Debug.Print Me.Height
      End Sub

      I am able to minimize the userform such that only the window title remains. Upon closing this minimized userform _terminate reports width = 123 and height = 165.6 on a 4k as well as a FHD monitor. What is even more baffling: the windows title remaining looks like a rectangle in landscape orientation but height is reported > width.

      Well, as small as the window title does not make much sense anyway as no form content is shown but I was curious and hopefully this information triggers new ideas for whatever was in your way when you made your discovery.

      Cheers, Volker

      Reply
  3. Thank you for the wonderful post. It is very helpful.

    I tried adding a textbox under the listbox, it does resize but somehow it does not maintian the it’s position on the userform, seems it overlaps it.

    Could you please suggest?

    Code below:
    Private Sub lblResizer_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    ‘Move Textbox6
    Frm_SQL.TextBox6.Width = Frm_SQL.TextBox6.Width + X – MouseX
    Frm_SQL.TextBox6.Height = Frm_SQL.TextBox6.Height + Y – MouseY

    Many thanks & regards,
    Don

    Reply
    • Hi Don – I’ve not tested your code, but if you’re placing it below another object that is resizing, you’ll need to also change the Top property to remain a consistent distance below the object above.

      Reply
  4. This has been fantastic! But I have one problem. My user form contains a listbox. I am trying to save the listbox size and position, then resize and position it using a min, max, resize in the title bar.

    Whenever i try to min or max, or resize the form. the listbox size reverts to a really large width.
    When I first initialize the form, I am able to control size of list box, but min, max, resize reverts listbox to the really big width.
    Any ideas?

    Reply
    • There is nothing specific about ListBoxes that would cause any problems to my knowledge. Unfortunately, I think it’s a problem with your logic / calculations / approach. It to work it out too. You could try Downloading the example file and using the code that’s already there.

      One last thought… are you using a Mac? If so it may behave differently (and often does). I’ve not tested it on a Mac.

      Reply

Leave a Comment