Resizing Userforms in 64-bit excel

R

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
437
I have found an code snippit that describes resizing a userform (without resizing the controls) but it seems not to work when you run it on 64-bit systems. I'm getting a compile error (see below)

VBA Code: 
'Written: February 14, 2011
'Author:  Leith Ross
'
'NOTE:  This code should be executed within the UserForm_Activate() event.
' John note:  This code plays around with the API in order to size userforms

#If VBA7 Then

    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr

    Private Declare PtrSafe Function GetWindowLong _
        Lib "user32.dll" _
        (ByVal hWnd As LongPtr, _
        ByVal nIndex As LongPtr) _
    As LongPtr
               
    Private Declare PtrSafe Function SetWindowLong _
        Lib "user32.dll" _
        (ByVal hWnd As LongPtr, _
        ByVal nIndex As LongPtr, _
        ByVal dwNewLongPtr As LongPtr) _
    As LongPtr

    Private Const WS_THICKFRAME As LongPtr = &H40000
    Private Const GWL_STYLE As LongPtr = -16

#Else

    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As Long

    Private Declare PtrSafe Function GetWindowLong _
        Lib "user32.dll" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, _
        ByVal nIndex As Long) _
     As Long
               
    Private Declare PtrSafe Function SetWindowLong _
        Lib "user32.dll" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) _
    As Long
  
    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16

#End If


Public Sub MakeFormResizable()

  Dim lStyle As Long
  Dim hWnd As Long
  Dim RetVal
  
    hWnd = GetForegroundWindow  '<- Compile Error Type Mismatch
  
    'Get the basic window style
     lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME

    'Set the basic window styles
     RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)

End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Thanks ND - that was embarrassingly easy. But perhaps this will be more of a challenge? When I run the code, the first error is gone but I get the following this time:

VBA Code: 
'Written: February 14, 2011
'Author:  Leith Ross
'
'NOTE:  This code should be executed within the UserForm_Activate() event.
' John note:  This code plays around with the API in order to size userforms

#If VBA7 Then

    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr

    Private Declare PtrSafe Function GetWindowLong _
        Lib "user32.dll" _
        (ByVal hWnd As LongPtr, _
        ByVal nIndex As LongPtr) _
    As LongPtr
               
    Private Declare PtrSafe Function SetWindowLong _
        Lib "user32.dll" _
        (ByVal hWnd As LongPtr, _
        ByVal nIndex As LongPtr, _
        ByVal dwNewLongPtr As LongPtr) _
    As LongPtr

    Private Const WS_THICKFRAME As LongPtr = &H40000
    Private Const GWL_STYLE As LongPtr = -16

#Else

    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As Long

    Private Declare PtrSafe Function GetWindowLong _
        Lib "user32.dll" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, _
        ByVal nIndex As Long) _
     As Long
               
    Private Declare PtrSafe Function SetWindowLong _
        Lib "user32.dll" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) _
    As Long
  
    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16

#End If


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  '<- Run-time error '453' - Can't find DLL entry point GetWindowLong in user32.dll

    'Set the basic window styles
     RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)  '<- I imagine the error above will happen here too but in the SetWindowLong variant?

End Sub
 
Upvote 1
Works perfectly!!!!

VBA Code: 
#If VBA7 Then

    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr

    #If Win64 Then
        Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #Else
        Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #End If
    
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #Else
        Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #End If

    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16

#Else

    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As Long

    Private Declare PtrSafe Function GetWindowLong _
        Lib "user32.dll" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, _
        ByVal nIndex As Long) _
     As Long
               
    Private Declare PtrSafe Function SetWindowLong _
        Lib "user32.dll" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) _
    As Long
  
    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16

#End If


Public Sub MakeFormResizable()

  Dim lStyle As LongPtr
  Dim hWnd As LongPtr
  Dim RetVal As LongPtr
  
    hWnd = GetForegroundWindow
  
    'Get the basic window style
     lStyle = GetWindowLongPtr(hWnd, GWL_STYLE) Or WS_THICKFRAME

    'Set the basic window styles
     RetVal = SetWindowLongPtr(hWnd, GWL_STYLE, lStyle)

End Sub
 
Upvote 0
RawlinsCross said:
Works perfectly!!!!

VBA Code: 
#If VBA7 Then

    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr

    #If Win64 Then
        Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #Else
        Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #End If
   
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #Else
        Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #End If

    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16

#Else

    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As Long

    Private Declare PtrSafe Function GetWindowLong _
        Lib "user32.dll" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, _
        ByVal nIndex As Long) _
     As Long
              
    Private Declare PtrSafe Function SetWindowLong _
        Lib "user32.dll" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) _
    As Long
 
    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16

#End If


Public Sub MakeFormResizable()

  Dim lStyle As LongPtr
  Dim hWnd As LongPtr
  Dim RetVal As LongPtr
 
    hWnd = GetForegroundWindow
 
    'Get the basic window style
     lStyle = GetWindowLongPtr(hWnd, GWL_STYLE) Or WS_THICKFRAME

    'Set the basic window styles
     RetVal = SetWindowLongPtr(hWnd, GWL_STYLE, lStyle)

End Sub
Click to expand...
This function runs REALLY SLOW on Windows 11 64-bit... Maybe 15-20s on my machine.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jaafar Tribak
  • Question Question
KeyPress Event for Worksheet Cells (Subclassing Without Crashing)
Replies
0
Views
252
Jaafar Tribak
Jaafar Tribak
S
  • Solved
Hide userform title bar, and locking the userform into a relative position on the application.
2
Replies
16
Views
480
Jaafar Tribak
Jaafar Tribak
Jaafar Tribak
  • Question Question
Capturing the Paste Event (API Hooking - Redirection)
Replies
4
Views
789
Jaafar Tribak
Jaafar Tribak
D
  • Solved
Long vs Longptr
Replies
7
Views
329
Micron
M
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
328
Minh Tuan VN
M

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

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