Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 29

Thread: Resize a Userform

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

    Default Re: Resize a Userform

    Hello Fane,

    I update the macro for both 32 and 64 bit windows. This macro code requires VBA7 or it will error during compilation.

    Place this code in a separate VBA module and call the macro "MakeForResizable" from the UserForm_Activate event.

    Code:
    ' Written:  July 21, 2019
    ' Author:   Leith Ross
    ' NOTE:     This code should be executed within the UserForm_Activate() event.
    '           This code works with both 32 bit and 64 bit windows. Requires VBA7.
    
    
    Public Const SW_HIDE            As Long = 0
    Public Const SW_NORMAL          As Long = 1
    Public Const SW_SHOWMINIMIZED   As Long = 2
    Public Const SW_MAXIMIZED       As Long = 3
    
    
    Public Const WS_MAXIMIZEBOX     As Long = &H10000
    Public Const WS_MINIMIZEBOX     As Long = &H20000
    Public Const WS_THICKFRAME      As Long = &H40000
    Public Const WS_MAXIMIZE        As Long = &H1000000
    Public Const GWL_STYLE          As Long = -16
    
    
    
    
    #If  Win64 Then
    
    
        Public Declare Function GetWindowLong _
            Lib "user32.dll" Alias "GetWindowLongPtr" _
                (ByVal hwnd As LongPtr, _
                 ByVal nIndex As Long) _
            As LongPtr
    
    
        Public Declare Function SetWindowLong _
            Lib "user32.dll" Alias "SetWindowLongPtr" _
                (ByVal hwnd As LongPtr, _
                 ByVal nIndex As Long, _
                 ByVal dwNewLong As LongPtr) _
            As LongPtr
    
    
        Public Declare PtrSafe Function ShowWindow _
            Lib "user32.dll" _
                (ByVal hwnd As LongPtr, _
                 ByVal nCmdShow As Long) _
            As Long
            
        Public Declare Function GetForegroundWindow Lib "user32.dll" () As LongPtr
        
        Public Sub MakeFormResizable()
    
    
            Dim lStyle  As LongPtr
            Dim hwnd    As LongPtr
            Dim RetVal  As LongPtr
      
                hwnd = GetForegroundWindow
      
                'Get the basic window style
                lStyle = GetWindowLong(hwnd, GWL_STYLE) Or WS_THICKFRAME Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX
    
    
                'Set the basic window styles
                RetVal = SetWindowLong(hwnd, GWL_STYLE, lStyle)
    
    
        End Sub
    
    
        Public Sub MakeFormStatic()
    
    
            Dim lStyle  As Long
            Dim hwnd    As Long
            Dim RetVal  As LongPtr
      
                hwnd = GetForegroundWindow
      
                'Get the basic window style
                lStyle = GetWindowLong(hwnd, GWL_STYLE) And (Not WS_THICKFRAME)
    
    
                'Set the basic window styles
                RetVal = SetWindowLong(hwnd, GWL_STYLE, lStyle)
    
    
        End Sub
        
        
    #Else 
                   
        Public Declare Function GetWindowLong _
            Lib "user32.dll" Alias "GetWindowLongA" _
                (ByVal hwnd As Long, _
                 ByVal nIndex As Long) _
            As LongPtr
        
        Public Declare Function SetWindowLong _
            Lib "user32.dll" Alias "SetWindowLongA" _
                (ByVal hwnd As Long, _
                 ByVal nIndex As Long, _
                 ByVal dwNewLong As Long) _
            As Long
    
    
        Public Declare Function ShowWindow _
        Lib "user32.dll" _
            (ByVal hwnd As Long, _
             ByVal nCmdShow As Long) _
        As Long
    
    
        Public Declare Function GetForegroundWindow Lib "user32.dll" () As Long
    
    
        Public Sub MakeFormResizable()
    
    
            Dim lStyle  As Long
            Dim hwnd    As Long
            Dim RetVal  As Long
      
                hwnd = GetForegroundWindow
      
                'Get the basic window style
                lStyle = GetWindowLong(hwnd, GWL_STYLE) Or WS_THICKFRAME Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX
    
    
                'Set the basic window styles
                RetVal = SetWindowLong(hwnd, GWL_STYLE, lStyle)
    
    
        End Sub
    
    
        Public Sub MakeFormStatic()
    
    
            Dim lStyle As Long
            Dim hwnd As Long
            Dim RetVal
      
                hwnd = GetForegroundWindow
      
                'Get the basic window style
                lStyle = GetWindowLong(hwnd, GWL_STYLE) And (Not WS_THICKFRAME)
    
    
                'Set the basic window styles
                RetVal = SetWindowLong(hwnd, GWL_STYLE, lStyle)
    
    
        End Sub
    
    
    #End  If
    Sincerely,
    Leith Ross

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

    Default Re: Resize a Userform

    Hello Fane,

    Use the code in this post. In the previous post I missed adding a couple of PtrSafe keywords.

    Code:
    ' Written:  July 21, 2019
    ' Author:   Leith Ross
    ' NOTE:     This code should be executed within the UserForm_Activate() event.
    '           This code works with both 32 bit and 64 bit windows. Requires VBA7.
    
    
    Public Const SW_HIDE            As Long = 0
    Public Const SW_NORMAL          As Long = 1
    Public Const SW_SHOWMINIMIZED   As Long = 2
    Public Const SW_MAXIMIZED       As Long = 3
    
    Public Const WS_MAXIMIZEBOX     As Long = &H10000
    Public Const WS_MINIMIZEBOX     As Long = &H20000
    Public Const WS_THICKFRAME      As Long = &H40000
    Public Const WS_MAXIMIZE        As Long = &H1000000
    Public Const GWL_STYLE          As Long = -16
    
    
    #If  Win64 Then
    
    
        Public Declare PtrSafe Function GetWindowLong _
            Lib "user32.dll" Alias "GetWindowLongPtr" _
                (ByVal hwnd As LongPtr, _
                 ByVal nIndex As Long) _
            As LongPtr
    
    
        Public Declare PtrSafe Function SetWindowLong _
            Lib "user32.dll" Alias "SetWindowLongPtr" _
                (ByVal hwnd As LongPtr, _
                 ByVal nIndex As Long, _
                 ByVal dwNewLong As LongPtr) _
            As LongPtr
    
    
        Public Declare PtrSafe Function ShowWindow _
            Lib "user32.dll" _
                (ByVal hwnd As LongPtr, _
                 ByVal nCmdShow As Long) _
            As Long
            
        Public Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr
      
    
        Public Sub MakeFormResizable()
    
            Dim lStyle  As LongPtr
            Dim hwnd    As LongPtr
            Dim RetVal  As LongPtr
      
                hwnd = GetForegroundWindow
      
                'Get the basic window style
                lStyle = GetWindowLong(hwnd, GWL_STYLE) Or WS_THICKFRAME Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX
    
    
                'Set the basic window styles
                RetVal = SetWindowLong(hwnd, GWL_STYLE, lStyle)
    
        End Sub
    
    #Else 
                   
        Public Declare Function GetWindowLong _
            Lib "user32.dll" Alias "GetWindowLongA" _
                (ByVal hwnd As Long, _
                 ByVal nIndex As Long) _
            As Long
        
        Public Declare Function SetWindowLong _
            Lib "user32.dll" Alias "SetWindowLongA" _
                (ByVal hwnd As Long, _
                 ByVal nIndex As Long, _
                 ByVal dwNewLong As Long) _
            As Long
    
    
        Public Declare Function ShowWindow _
        Lib "user32.dll" _
            (ByVal hwnd As Long, _
             ByVal nCmdShow As Long) _
        As Long
    
    
        Public Declare Function GetForegroundWindow Lib "user32.dll" () As Long
    
    
        Public Sub MakeFormResizable()
    
            Dim lStyle  As Long
            Dim hwnd    As Long
            Dim RetVal  As Long
      
                hwnd = GetForegroundWindow
      
                'Get the basic window style
                lStyle = GetWindowLong(hwnd, GWL_STYLE) Or WS_THICKFRAME Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX
    
    
                'Set the basic window styles
                RetVal = SetWindowLong(hwnd, GWL_STYLE, lStyle)
    
        End Sub
    
    #End  If
    Last edited by Leith Ross; Jul 21st, 2019 at 04:59 PM.
    Sincerely,
    Leith Ross

  3. #13
    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

    Hi Leith,

    Firstly thanks for your prompt reply!
    Secondly, I am not sure (anymore) that I know how to properly use the code, even if it seems to be simple...
    I thought that if the 'MakeFormResizable' is executed to the form Activate Event, it (the form) becomes resizable and just pressing a button keeping the next code, the Width and the Height of the form will be resized according to the Zoom value:
    Code:
     me.Zoom = 80
    1. I tried it in a 32 bit installation and, like the 'MakeFormResizable' procedure has not been executed, only controls of the form are zoomed according to the need...
    2. When I tried it on a 64 bit installation (VBA 7.1 - Windows 10 Professional 64bit), in fact CorelDRAW VBA, but this should not theoretically change anything, I received the next error: "Can't find DLL entry point for GetWindowLongPtr in user32.dll", on the line 'lStyle = GetWindowLong(meHwnd,....".

    I told you in my previous mail, I looked on the internet for the GetWindowLong for 64 bit and I found out that it doesn't exist anymore and it must be replaced with GetWindowLongPtr. But I could find only Get(Set)WindowLongPtrA and Get(Set)WindowLongPtrW functions... That's why I told you in my in my initial comment I tried Get(Set)WindowLongPtrA. It looked to use the same parameters...
    I didn't received an error, the 'RetVal' variable was not zero (but negative...) and I thought I made a mistake, in a way...

    So, since it does not work as (I) expected (in 32 bit neither), can you have some idea about the mystery of 1) issue first and 2) issue, after?
    Should I resize the form only on the form initialization/activate event? Doesn't this resizing stay valid for all the form life? Am I doing something wrong in terms of approach?

    Thanks in advance!

  4. #14
    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

    I replaced the Alias in the functions declaration (Alias "Get[Set]WindowLongPtrA") and it works without throwing any error, but please enlighten me on the first issue...
    This time the variables value are:
    lStyle = 2496594048
    RetVal = -1798832000, the same as I obtain in my initial try... being negative I am not sure it is what it should be. I will check now the RetVal value in Excel 32 bit... In Excel they are:
    lStyle = -1798373248
    RetVal = -1798832000

    How can I properly use the resizing code? Isn't it not enough to use me.Zoom = xx, anytime on the form life, if 'MakeFormResizable' has been executed at the Activate form Event?

    Thanks!
    Last edited by Fane Duru; Jul 22nd, 2019 at 04:31 AM.

  5. #15
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,346
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Resize a Userform

    The API declarations have some errors in them.

    See if this works for you:

    Code in a standard module:
    Code:
    Option Explicit
    
    #If  VBA7 Then
        #If  Win64 Then
            Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
            Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
        #Else 
            Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
            Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
        #End  If
        Declare PtrSafe Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As LongPtr) As Long
        Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hwnd As LongPtr) As Long
    #Else 
        Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
        Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
        Declare Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As Long) As Long
        Declare Function DrawMenuBar Lib "user32" Alias "DrawMenuBar" (ByVal hwnd As Long) As Long
    #End  If
    
    Const WS_MAXIMIZEBOX     As Long = &H10000
    Const WS_MINIMIZEBOX     As Long = &H20000
    Const WS_THICKFRAME      As Long = &H40000
    Const WS_MAXIMIZE        As Long = &H1000000
    Const GWL_STYLE          As Long = -16
    
    
    Public Property Let MakeFormResizable(ByVal Form As UserForm, ByVal Resizable As Boolean)
        #If  VBA7 Then
            Dim hwnd As LongPtr
        #Else 
            Dim hwnd As Long
        #End  If
        
        WindowFromAccessibleObject Form, hwnd
        If Resizable Then
            Call SetWindowLong(hwnd, GWL_STYLE, GetWindowLong(hwnd, GWL_STYLE) Or WS_THICKFRAME Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX)
        Else
            Call SetWindowLong(hwnd, GWL_STYLE, GetWindowLong(hwnd, GWL_STYLE) And (Not WS_THICKFRAME) And (Not WS_MINIMIZEBOX) And (Not WS_MAXIMIZEBOX))
        End If
        DrawMenuBar hwnd
    End Property

    You call it from the UserForm module like this :
    Code:
    Private Sub UserForm_Initialize()
        MakeFormResizable(Me) = True
    End Sub
    To make the UserForm NOT resizable, just set the Property to False:
    MakeFormResizable(Me) = False



    You may be intersted in this thread for adjusting the size of the controls and text along with the userform:
    https://www.mrexcel.com/forum/excel-...ml#post5179915
    Last edited by Jaafar Tribak; Jul 22nd, 2019 at 07:19 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  6. #16
    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

    Thanks!

    I tried the code but I receive an error: 'Compile error: User-defined type not defined' on the declaration line of 'WindowFromAccessibleObject'. Should I have a specific reference?
    Anyhow, there must be a GetForegroundWindow or GetActiveWindow to define the form handler and GetWindowLong in order to get the existing form window style, I think...

    I also looked to the thread where the link is pointing. Does the code want to do what Me.Zoom does now for controls? The controls zooming works well. My problem is that the form itself dimensions are not modified accordingly. They are not modified at all...
    Last edited by Fane Duru; Jul 22nd, 2019 at 08:51 AM. Reason: To complete an unfinished idea

  7. #17
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,346
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Resize a Userform

    Strange ! that shouldn't happen because the project has a reference to the IAccessibility library by default .

    Anyway, try this ;

    Code:
    Declare PtrSafe Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As Any , phwnd As LongPtr) As Long
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  8. #18
    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

    Now, I do not receive any error, but the form is not resizable.
    Let me tell you what resizable means for me... I have a button trying to zoom the form keeping the code 'me.Zoom = 80'. When I press it, all controlls (except the form dimensions) are decreased at 80%. I need the form itself to decrease its dimensions with the same ratio...

    Is that what the code does, or my expectations are not appropriate for this piece of code?
    Last edited by Fane Duru; Jul 22nd, 2019 at 09:10 AM.

  9. #19
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,346
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Resize a Userform

    Quote Originally Posted by Fane Duru View Post
    Now, I do not receive any error, but the form is not resizable.
    Let me tell you what resizable mean for me... I have a button trying to zoom the form keeping the code 'me.Zoom = 80'. When I press it, all controlls (except the form dimensions) are decreased at 80%. I need the form itself to decrease its dimensions with the same ratio...
    That is a different subject.

    Have you tried decreasing the size of the userform to 80% ? Try with UserForm Width and Height Properties.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  10. #20
    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

    Only now I observed that the GetWindowLong is included inside of the SetWindowLong. And I did not `allocated' the right handler to the function...
    I will let you know it something will change.

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
  •