Resizable UserForm

Add to Favorites
Author: | Edits: don

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.

b295d738c82c2f837f25246841295fe7.jpg

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
UserForm Events
Tutorial: Explanation of UserForm Events in Excel. This includes what they are, how to use them, and...
UserForm Controls
Tutorial: This tutorial gives you an overview of what a UserForm Control is and how to add them to y...
Scrollable UserForm
Tutorial: How to make a scrollable UserForm. This allows you to put scroll bars onto a form so that ...
How to Resize Rows and Columns in Excel Quickly
Tutorial: Resizing rows and columns in Excel is an easy process. Simply left click in between the co...
Prevent Images and Shapes from Resizing or Moving in Excel
Tutorial: How to stop Images and Shapes from resizing in Excel when you change the size of rows and...
Quickly Resize Multiple Columns or Rows at Once in Excel
Tutorial: How to quickly resize multiple columns and rows at once in Excel.  This avoids having to...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course