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:
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 theFindWindowA()
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):
I then use the GetParent()
API call twice to hop up to the userform’s wrapper window:
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
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 .
LikeLike
That’s very cool, Jaafar – I certainly hadn’t seen this one before. Thanks for sharing it!
LikeLike
Hi, nice tutorial,
i need the handle of Frame on Worksheet and not userform, please.
LikeLike
Pingback: #Excel Super Links #75 – shared by David Hager | Excel For You
Pingback: #Excel Super Links #120 – shared by David Hager | Excel For You