Resize a Userform

Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Fane Duru said:
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...
Click to expand...

:) That is a different subject.

Have you tried decreasing the size of the userform to 80% ? Try with UserForm Width and Height Properties.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Resize userform - in increments
Replies
0
Views
48
Formula11
F
I
  • Solved
Last row in table resizes after new row inserted at top
Replies
3
Views
209
ipbr21054
I
B
  • Question Question
Opening userform
Replies
4
Views
149
RoryA
RoryA
S
  • Solved
Hide userform title bar, and locking the userform into a relative position on the application.
2
Replies
16
Views
463
Jaafar Tribak
Jaafar Tribak
M
  • Question Question
Modeless UserForm appears on wrong Excel workbook
Replies
5
Views
170
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,223,867
Messages
6,175,074
Members
452,611
Latest member
bls2024

Share this page

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