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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hello jimmy2timez,

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

Sincerely,
Leith Ross
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top