Resize a Userform

jimmy2timez

New Member
Joined
Apr 21, 2010
Messages
48
Is there a "simple" code that allows a userform to be resized after it is open?
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Rekd

Banned
Joined
Apr 28, 2010
Messages
136
Is there a "simple" code that allows a userform to be resized after it is open?
If it's the current form, use:
Code:
Me.Width = 115
If it's not the current form, use:
Code:
frmRename.Width = 115
You can assign the code to a button, a cell, a workbook, what ever you want to use to trigger the resize event.

Use Height instead of Width to change the height.
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
2010, 2007
Platform
Windows
Hello jimmy2timez,

Not really sure this what you want but here is a macro that will allow you to resize the UserForm by dragging any one of the sides. The code is do this is rather complex, but I have done that part for you. You only need to call the macro MakeFormResizable from the UserForm's Activate event code.

Calling the Macro from the UserForm
Code:
Private Sub UserForm_Activate()
  MakeFormResizable
End Sub
Macro Module Code
Code:
'Written: August 02, 2010
'Author:  Leith Ross
'Summary: Makes the UserForm resizable by dragging one of the sides. Place a call
'         to the macro MakeFormResizable in the UserForm's Activate event.

 Private Declare Function SetLastError _
   Lib "kernel32.dll" _
     (ByVal dwErrCode As Long) _
   As Long
   
 Public Declare Function GetActiveWindow _
   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

Public Sub MakeFormResizable()

  Dim lStyle As Long
  Dim hWnd As Long
  Dim RetVal
  
  Const WS_THICKFRAME = &H40000
  Const GWL_STYLE As Long = (-16)
  
    hWnd = GetActiveWindow
  
    'Get the basic window style
     lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME
     
    'Set the basic window styles
     RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)
    
    'Clear any previous API error codes
     SetLastError 0
    
    'Did the style change?
     If RetVal = 0 Then MsgBox "Unable to make UserForm Resizable."
     
End Sub
Sincerely,
Leith Ross
 

ljungren13

Board Regular
Joined
Jun 4, 2010
Messages
195
I have created Zoom comboboxes on a form before to resize to account for different resolutions
Code:
Private Sub ZoomComboBox_Change()
 Form.Zoom = Form.ZoomComboBox.Value
   Form.Height = Form.ZoomComboBox.Value / 100 * 500
   Form.Width = Form.ZoomComboBox.Value / 100 * 600
End Sub
In this code the 500 = Your overall height of the userform and the 600 = the overall width
 

jimmy2timez

New Member
Joined
Apr 21, 2010
Messages
48
ATTENTION: Leith Ross

That code worked great. Thanks for your help.
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
2010, 2007
Platform
Windows
Hello jimmy2timez,

Good to hear the code is what you needed and it is working for you. Glad I could help.

Sincerely,
Leith Ross
 

dpinto

New Member
Joined
Jun 6, 2012
Messages
1
Leith - 2 years later and it still works! I'm learning VBA while working on a project with a deadline. Your code was flawless and a godsend.

thanks

djpinto
 

dhingrapahul

New Member
Joined
Mar 13, 2015
Messages
1
Hi I tried to use this code with form in access, unfortunately it doesn't seem to be working :(
I am a beginner with VBA and need a code that can be used to resize a userform in access at runtime using mouse events.

Thanks
Pahul
 

OneSkyWalker

New Member
Joined
Feb 7, 2019
Messages
2
I never thought I would learn enough about Excel to contribute to this forum, but here goes ...

I believe it to be worth noting that to increase the number of characters of text which can be displayed in a UserForm progress bar window, it is insufficient to make the window wider with (for example) 'UserForm.Width = Application.Max(UserForm.Width, Application.UsableWidth / 3)'.

One must also make the UserForm.Label field wider with (for example) 'UserForm.Label.Width = Application.Max(UserForm.Label.Width, UserForm.InsideWidth * 0.9)'. The UserForm.Label field is, of course, the field in which text gets displayed within the UserForm window.

When making the UserForm.Label field wider, it also makes sense to make the ProgressBar field just as wide with (for example) 'UserForm.ProgressBar.Width = Application.Max(UserForm.ProgressBar.Width, UserForm.Label.Width)'.

It is a crying shame that one can't find this kind of advice in Microsoft's Excel VBA documentation! :(

Bill, thanks for hosting MrExcel.com! It is an incredibly valuable resource! I would never have been able to learn Excel VBA without it. :)
 

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
It is a historical thread, but I tested your code to make form resizable (for 64 bit) but I did not succeed making it work.
I adapted the functions declaration and replaced GetWindowLong and SetWindowLong with GetWindowLongPtrA and SetWindowLongPtrA but the form is not resizable...
lStyle and RetVal both are -1798832000.
Did you try this solution for 64 bit?
Is it a way to make it working?

Thanks in advance!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,781
Messages
5,488,847
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top