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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

W
  • Question
minimize and Maximize
Replies
4
Views
649
NdNoviceHlp
NdNoviceHlp
H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
275
huiettcm
H
A
  • Question
What is wrong with this VBA code: userForm get hand ico for pointer on hover
2
Replies
15
Views
497
RoryA
RoryA
Jaafar Tribak
  • Question
Blurring images using Direct2D & Windows Imaging Component
Replies
3
Views
236
Dan_W
D
netspeedz
  • Solved
Load Image in User Form - 64bit Excel [VBA]
Replies
6
Views
346
netspeedz
netspeedz

Forum statistics

Threads
1,214,591
Messages
6,120,427
Members
448,961
Latest member
nzskater

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