Resize a Userform

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
2010, 2007
Platform
Windows
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.

Rich (BB 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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Leith Ross

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

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

Rich (BB 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:

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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!
 

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
The API declarations have some errors in them.

See if this works for you:

Code in a standard module:
Code:
Option Explicit

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  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
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
        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
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  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
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    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
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  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)
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
        Dim hwnd As LongPtr
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
        Dim hwnd As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  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-questions/1078144-autosizing-userform-display.html#post5179915
 
Last edited:

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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 [B][COLOR=#ff0000]As Any[/COLOR][/B] , phwnd As LongPtr) As Long
 

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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.
 

Fane Duru

New Member
Joined
Sep 3, 2002
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,301
Messages
5,486,047
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top