Getting A Handle On Userforms [VBA]

In a previous post I wrote about how to add minimize and maximize buttons to a VBA userform. A necessary part of that process is to get the handle to the userform’s window, but the focus of that post was how to correctly declare the GetWindowLong() and SetWindowLong() API calls across 32-bit and 64-bit environments. Today I’m going to look at getting the hWnd of a userform in more detail.

A handle to a window is also called an hWnd. We quite often need the userform’s hWnd so we can use Windows API functions to give the userform features which aren’t available out-of-the-box – such as the minimize and maximize buttons illustrated on that other post.

If you use a tool such as Spy++ you’ll find that a bare userform is actually comprised of two windows:

UserformWindowsSpy

The red, “wrapper” window has a ThunderDFrame class (ThunderXFrame pre Excel 2000). When we talk about getting a userform’s hWnd, this is the window handle we’re looking for.

Option 1 – Get The hWnd From Within A Userform Class Module

The way I get a userform’s hWnd from within its own class module is:

  • Retrieve it in the userform’s Initialize() event handler using the FindWindowA() function
  • Store it in a private, module level variable
  • Expose it as a read-only property should external code need to use it

Userform Class Module called Userform1

Option Explicit

#If Win64 Then

    Private Declare PtrSafe Function FindWindowA _
        Lib "user32.dll" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As LongPtr

    Private mlnghWnd As LongPtr

    Public Property Get hWnd() As LongPtr
        hWnd = mlnghWnd
    End Property

#Else

    Private Declare Function FindWindowA _
        Lib "user32.dll" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long

    Private mlnghWnd As Long

    Public Property Get hWnd() As Long
        hWnd = mlnghWnd
    End Property

#End If

Private Sub UserForm_Initialize()

    StorehWnd

End Sub

Private Sub StorehWnd()

    Dim strCaption As String
    Dim strClass As String

    'class name changed in Office 2000
    If Val(Application.Version)>=9 Then
        strClass = "ThunderDFrame"
    Else
        strClass = "ThunderXFrame"
    End If

    'remember the caption so we can 
    'restore it when we're done
    strCaption = Me.Caption

    'give the userform a random
    'unique caption so we can reliably
    'get a handle to its window
    Randomize
    Me.Caption = CStr(Rnd)

    'store the handle so we can use 
    'it for the userform's lifetime
    mlnghWnd = FindWindowA(strClass , Me.Caption)

    'set the caption back again
    Me.Caption = strCaption

End Sub

Standard Code Module

Option Explicit

Sub Example()

    Dim ufmExample As Userform1

    Set ufmExample = New Userform1

    Debug.Print ufmExample.hWnd

End Sub

The code gives the userform a temporary, random (and hopefully unique) caption to ensure that the FindWindowA() function gets the correct window. You could go to town with ways of guaranteeing it is unique but, for me, just using the Rnd() function is more than sufficient.

A nice feature about storing it in a module level variable is that you don’t need to worry about state loss clearing the variable: if a state loss event occurs then the entire userform object will be destroyed anyway. The hWnd property naturally lends itself to being read-only because it is set internally and will never change.

Option 2 – Get The hWnd From Within A Standard Code Module

The above code will work perfectly fine and you can implement it in every userform that needs it. However, it would also be nice to have a generic function in a standard code module that accepts a reference to a userform instance and returns back the hWnd.

Getting the hWnd in a generic function is a much trickier business. If you search the internet you’ll find some pretty complicated code which tries to do it, but I haven’t seen anything out there which is truly robust. I do, however, have a hacky approach which is relatively simple and will work:

Option Explicit

#If Win64 Then
    Private Declare PtrSafe Function GetParent _
        Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Private Declare Function GetParent _
        Lib "user32.dll" (ByVal hwnd As Long) As Long
#End If

#If Win64 Then
    Public Function GetUserformHwnd( _
        ByRef ufmTarget As MSForms.UserForm) As LongPtr
    
    Dim lngHwndControl As LongPtr
        
#Else
    Public Function GetUserformHwnd( _
        ByRef ufmTarget As MSForms.UserForm) As Long
    
    Dim lngHwndControl As Long
    
#End If

    Dim objControl As MSForms.Control
    Dim strRandomName As String
    
    Randomize
    strRandomName = CStr(Rnd)
    
    Set objControl = ufmTarget.Controls.Add( _
        "Forms.Frame.1", strRandomName, False)
    
    objControl.Left = ufmTarget.ScrollLeft + ufmTarget.InsideWidth
    objControl.Visible = True
    lngHwndControl = objControl.[_GethWnd]
    
    GetUserformHwnd = GetParent(GetParent(lngHwndControl))
    ufmTarget.Controls.Remove strRandomName
    
End Function

The code looks more complicated than it really is due to the conditional compilation sections. The function adds an MSForms.Frame control to the userform. MSForms.Frame controls have an hWnd (it’s important to note that most MSForms controls do not have their own window), so I can reference it using a general MSFroms.Control variable type and retrieve its handle using the hidden MSForms.Control.GethWnd() method (saves an extra API call):

ControlGetHwnd

I then use the GetParent() API call twice to hop up to the userform’s wrapper window:

UserformWindowsFrameSpy

For all of this to work the frame control must be visible, so I slip it off to the right of the visible area of the userform before making it visible to avoid it flashing up in front of the user. Finally I clean up and delete the temporary frame control by removing it from the userform’s Controls collection.

Since "Userform1" is a TypeOf MSForms.Userform, I can call the function from within a userform class module or from a standard code module – for example, looping through the VBA.Userforms collection:

Option Explicit

'example 1 - call from a userform class module
'using the Me keyword
Private Sub UserForm_Initialize()
    
    Debug.Print GetUserformHwnd(Me)
    
End Sub
Option Explicit

'example 2 - code in a standard code module
Sub test()
    
    Dim ufm1 As UserForm1
    Dim ufm2 As UserForm1
    Dim lngUserform As Long
    
    Set ufm1 = New UserForm1
    Set ufm2 = New UserForm1
    
    For lngUserform = 0 To UserForms.Count - 1
        Debug.Print GetUserformHwnd(UserForms(lngUserform))
    Next lngUserform
    
End Sub
Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , , , . Bookmark the permalink.

5 Responses to Getting A Handle On Userforms [VBA]

  1. Jaafar Tribak says:

    Hi Colin,
    Nice Workaround !

    Another much simpler approach is to use the little known IUnknown_GetWindow API that is part of the Shlwapi Library .. Something like this would get you the correct HWNDs :

    Option Explicit

    #If Win64 Then
    Private Declare PtrSafe Function IUnknown_GetWindow Lib _
    “shlwapi” Alias “#172” (ByVal pIUnk As IUnknown, ByVal hwnd As LongPtr) As Long
    #Else
    Private Declare Function IUnknown_GetWindow Lib _
    “shlwapi” Alias “#172” (ByVal pIUnk As IUnknown, ByVal hwnd As Long) As Long
    #End If

    Public Function GetUserformHwnd( _
    ByVal ufmTarget As MSForms.UserForm) As Long
    IUnknown_GetWindow ufmTarget, VarPtr(GetUserformHwnd)
    End Function

    Sub test()

    Dim ufm1 As UserForm1
    Dim ufm2 As UserForm1
    Dim lngUserform As Long

    Set ufm1 = New UserForm1
    Set ufm2 = New UserForm1

    For lngUserform = 0 To UserForms.Count – 1
    Debug.Print GetUserformHwnd(UserForms(lngUserform))
    Next lngUserform

    End Sub

    Note that this is one of those dll functions that are exported by its ordinal number.

    see here : https://msdn.microsoft.com/en-us/library/windows/desktop/bb773814(v=vs.85).aspx

    You would expect this to conviniently work for getting the hwnd of other windowed COM objects such as the much needed hwnd of a workbook window but unfortunately it doesn’t .. Only works for a limited number of Interfaces .

    Like

  2. Hi, nice tutorial,
    i need the handle of Frame on Worksheet and not userform, please.

    Like

  3. Pingback: #Excel Super Links #75 – shared by David Hager | Excel For You

  4. Pingback: #Excel Super Links #120 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s