[VBA] How to Make UserForms Maximize, Minimize, and Resizable (Windows API)

2025年12月29日

Introduction

By default, VBA UserForms have a fixed window size and only a “Close” button in the title bar.

However, allowing users to freely change the size according to the amount of data displayed, or temporarily minimize the form to return to sheet operations, significantly improves the tool’s usability.

These standard Windows features—”Maximize,” “Minimize,” and “Resize”—can be added to a UserForm later by utilizing the Windows API.

This article explains advanced techniques to change the UserForm’s window style, add maximize/minimize buttons to the title bar, and enable resizing by dragging the window frame.

VBA Sample Code to Enable Maximize, Minimize, and Resize

This macro consists of Declare statements, Const (constants), and the form’s _Activate() event procedure. API-related definitions are generally written in a Standard Module.

Step 1: Write API Declarations in a Standard Module

'--- Write in a Standard Module (e.g., Module1) ---

' Compatible with both 64-bit and 32-bit
#If VBA7 Then
    Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Public Declare PtrSafe Function GetWindowLongPtr Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
    Public Declare PtrSafe Function SetWindowLongPtr Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    Public Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hwnd As LongPtr) As Long
#Else
    Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    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
    ' Set aliases for Ptr functions in 32-bit environment
    Public Const SetWindowLongPtr = SetWindowLong
    Public Const GetWindowLongPtr = GetWindowLong
#End If

' Constant for getting/setting window style
Public Const GWL_STYLE As Long = -16
' Style with a resizable thick frame
Public Const WS_THICKFRAME As Long = &H40000
' Style with a maximize button
Public Const WS_MAXIMIZEBOX As Long = &H10000
' Style with a minimize button
Public Const WS_MINIMIZEBOX As Long = &H20000

Step 2: Write the UserForm Event Procedure

Write this code directly into the UserForm’s code module. Use the Activate event where the window’s existence is guaranteed.

[UserForm: ResizableForm]

' Event executed when the form becomes active
Private Sub UserForm_Activate()
    Dim formHandle As LongPtr
    Dim currentStyle As LongPtr
    
    ' 1. Get the window handle of this form
    ' "ThunderDFrame" is the class name for UserForms
    formHandle = FindWindow("ThunderDFrame", Me.Caption)
    If formHandle = 0 Then Exit Sub
    
    ' 2. Get the current window style
    currentStyle = GetWindowLongPtr(formHandle, GWL_STYLE)
    
    ' 3. Add new style attributes to the current style
    '    Use the Or operator to turn on necessary attribute bits
    SetWindowLongPtr formHandle, GWL_STYLE, currentStyle Or WS_THICKFRAME Or WS_MAXIMIZEBOX Or WS_MINIMIZEBOX
    
    ' 4. Redraw the menu bar to apply changes
    DrawMenuBar formHandle
End Sub

Explanation of the Code

API Functions and Activate Event

The FindWindow, GetWindowLongPtr, SetWindowLongPtr, and DrawMenuBar APIs are functions for retrieving and modifying window information. Handling the window handle is safest in the Activate event, which occurs after the form is fully displayed.

`currentStyle Or WS_THICKFRAME Or WS_MAXIMIZEBOX Or WS_MINIMIZEBOX`

This is the core part where window styles are added.

Window Style Constants:

WS_THICKFRAME: Attribute for a thick frame that allows resizing.
WS_MAXIMIZEBOX: Attribute for having a maximize button.
WS_MINIMIZEBOX: Attribute for having a minimize button.

Or Operator: The bitwise Or is used to “add (combine)” multiple style attributes. By using Or against the currentStyle, the attribute bits for WS_THICKFRAME etc. are turned on. This allows adding new features while maintaining the original style.

This is the opposite operation to using And (Not ...) to “remove” style attributes (such as when removing the title bar).

Summary

This article explained advanced customization methods to add “Maximize,” “Minimize,” and “Resize” functions to VBA UserForms using the Windows API.

Window style information can be manipulated with GetWindowLongPtr and SetWindowLongPtr APIs.
Resize, Maximize, and Minimize correspond to the style attributes WS_THICKFRAME, WS_MAXIMIZEBOX, and WS_MINIMIZEBOX, respectively.
Use the bitwise Or operator to add new attributes to the existing style.

Using this technique allows for the construction of flexible, user-friendly UIs that behave more like standard Windows applications, transcending the limitations of standard UserForms.

よかったらシェアしてね！