Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Resize a Userform

  1. #1
    New Member jimmy2timez's Avatar
    Join Date
    Apr 2010
    Location
    Detroit, Michigan
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Resize a Userform

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

  2. #2
    Banned
    Join Date
    Apr 2010
    Location
    San Diego
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resize a Userform

    Quote Originally Posted by jimmy2timez View Post
    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.

  3. #3
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,823
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resize a Userform

    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

  4. #4
    Board Regular
    Join Date
    Jun 2010
    Posts
    195
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resize a Userform

    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

  5. #5
    New Member jimmy2timez's Avatar
    Join Date
    Apr 2010
    Location
    Detroit, Michigan
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resize a Userform

    ATTENTION: Leith Ross

    That code worked great. Thanks for your help.

  6. #6
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,823
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resize a Userform

    Hello jimmy2timez,

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

    Sincerely,
    Leith Ross

  7. #7
    New Member
    Join Date
    Jun 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resize a Userform

    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

  8. #8
    New Member
    Join Date
    Mar 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resize a Userform

    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

  9. #9
    New Member
    Join Date
    Feb 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Re: Resize a Userform

    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.

  10. #10
    New Member
    Join Date
    Sep 2002
    Location
    Bucharest, Romania
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Resize a Userform

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •