Resize a Userform

[jimmy2timez]

Is there a "simple" code that allows a userform to be resized after it is open?

 

[Rekd]

Is there a "simple" code that allows a userform to be resized after it is open?

If it's the current form, use:

Me.Width = 115

If it's not the current form, use:

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]

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

Private Sub UserForm_Activate()
  MakeFormResizable
End Sub

Macro Module 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]

I have created Zoom comboboxes on a form before to resize to account for different resolutions

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]

ATTENTION: Leith Ross

That code worked great. Thanks for your help.

 

[Leith Ross]

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]

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]

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]

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]

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!