How to get handle to a control on a spreadsheet?

[rdwray]

How do I get the handle for a combobox on a spreadsheet? I have tried ChildWindowFromPoint and RealChildWindowFromPoint without any luck. Thanks...

 

[Tom Urtis]

What do you mean "get the handle". If the combobox is from the Forms toolbar, just right click it and you can resize it with the handles that will appear, if that is what you mean. If the combobox is from the control tolbox, go into design mode and right click on the combobox.

If you mean something else altogether, please post back and explain what you are wanting to do.

 

[rdwray]

I am looking for the handle (number) that Windows uses internally to address the control, and all objects, through the API.

 

[Tom Urtis]

Still unclear exactly what you mean regarding how API comes into play, because controls have index numbers associated with them as a sheet property, based on the order they were added to the sheet.

So you can refer to the overall olecontrols index, example...

ActiveSheet.OLEObjects(1).Name

...will give you the name of the first control added to the sheet from the control toolbox.


This wil give you the third, if there are at least three
ActiveSheet.OLEObjects(3).Name

 

[rdwray]

I am familiar with Controls(x), but this does not return the handle to the control. Controls such as comboboxes and labels do not have IDs like a CommandBar does. If you go to VBE and utilize the debugger, you will see what I am talking about. See my first message where I addressed API functions and the API documentation itself for an explination. Thanks anyway.

 

[Tom Urtis]

I know what you mean about API handles, but other than a class module, which if you are familiar with API you'd likely be familiar and aware of class modules, I'm unfortunately unable to offer anything further, pending an understanding of what you want to do with the handles you are wanting to obtain for embedded controls.

 

[rdwray]

I have a listbox on a spreadsheet that I want to scroll with the MouseWheel. This is not a problem on a UserForm, but I cannot access it with a hook when on a spreadsheet without the handle.

 

[Jaafar Tribak]

Either use FindWindowEx twice and pass "F3 Server 60000000" Class name to get to the inner ListBox window but beware that this may return the wrong hwnd if there are more than one ListBox , TextBox or any other control with the same Class name in which case you will need to somehow identify the target ListBox control like its position on the screen or better its position related to the workbook window client area.

Also, you could use a hack by brievely moving the mouse cursor over the target listbox and retrieve its hwnd via the WindowFromPoint API.

I guess you are using a WH_MOUSE_LL system wide hook and Not Subclassing because the latter could easily crash your entire application !

You may find THIS interesting.

Regards.

 

[rdwray]

No, I am not subclassing and I have tried all suggestions and even downloaded 'This'. Apparently I am missing something because I still cannot scroll a listbox. I would think that GotFocus would make sure that it is the correct control, so the issue must be with FindWindow somewhere and it is being difficult.

 

[Jaafar Tribak]

Here is a working example that shows how to scroll with the mousewheel a worksheet ListBox . It uses a WH_MOUSE_LL Windows Hook which is set inside the ListBox_MouseMove event.

Have a look at this WORKBOOK DEMO.

Place this code (Main) in a Standard module:

Option Explicit
 
Private Type POINTAPI
  x As Long
  y As Long
End Type
 
Private Type MSLLHOOKSTRUCT
    pt As POINTAPI
    mouseData As Long
    flags As Long
    time As Long
    dwExtraInfo As Long
End Type
 
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetWindowLong Lib "user32.dll" _
Alias "GetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
 
Private Declare Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" _
(ByVal idHook As Long, _
ByVal lpfn As Long, _
ByVal hmod As Long, _
ByVal dwThreadId As Long) As Long
 
Private Declare Function CallNextHookEx Lib "user32" _
(ByVal hHook As Long, _
ByVal nCode As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
 
Private Declare Function UnhookWindowsHookEx Lib "user32" _
(ByVal hHook As Long) As Long
 
Private Declare Function PostMessage Lib "user32.dll" _
Alias "PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Declare Function WindowFromPoint Lib "user32" _
(ByVal xPoint As Long, _
ByVal yPoint As Long) As Long
 
Private Declare Function GetCursorPos Lib "user32.dll" _
(ByRef lpPoint As POINTAPI) As Long
 
 
Private Const WH_MOUSE_LL As Long = 14
Private Const WM_MOUSEWHEEL As Long = &H20A
Private Const HC_ACTION As Long = 0
Private Const GWL_HINSTANCE As Long = (-6)
 
Private Const WM_KEYDOWN As Long = &H100
Private Const WM_KEYUP As Long = &H101
Private Const VK_UP As Long = &H26
Private Const VK_DOWN As Long = &H28
Private Const WM_LBUTTONDOWN As Long = &H201
 
 
Private lMouseHook As Long
Private lListBoxhwnd As Long
Private bHookSet As Boolean
Private oListBox As MSForms.ListBox

Sub HookListBox(ListBox As MSForms.ListBox)
    
    Dim tPt As POINTAPI
    
    Set oListBox = ListBox
    GetCursorPos tPt
    lListBoxhwnd = (WindowFromPoint(tPt.x, tPt.y))
    PostMessage lListBoxhwnd, WM_LBUTTONDOWN, 0, 0
    If Not bHookSet Then
        lMouseHook = SetWindowsHookEx _
        (WH_MOUSE_LL, _
        AddressOf LowLevelMouseProc, GetAppInstance, 0)
        If lMouseHook <> 0 Then
            bHookSet = True
        End If
    End If
 
End Sub

Private Function LowLevelMouseProc _
(ByVal nCode As Long, ByVal wParam As Long, _
ByRef lParam As MSLLHOOKSTRUCT) As Long
 
    On Error Resume Next
    
    If (nCode = HC_ACTION) Then
        If WindowFromPoint _
            (lParam.pt.x, lParam.pt.y) = lListBoxhwnd Then
            If wParam = WM_MOUSEWHEEL Then
                LowLevelMouseProc = True
                If lParam.mouseData > 0 Then
                    PostMessage _
                    lListBoxhwnd, WM_KEYDOWN, VK_UP, 0
                    PostMessage _
                    lListBoxhwnd, WM_KEYUP, VK_UP, 0
                Else
                    PostMessage _
                    lListBoxhwnd, WM_KEYDOWN, VK_DOWN, 0
                    PostMessage _
                    lListBoxhwnd, WM_KEYUP, VK_UP, 0
                End If
                Exit Function
            End If
        Else
                UnhookWindowsHookEx lMouseHook
                bHookSet = False
        End If
    End If
    
    LowLevelMouseProc = _
    CallNextHookEx _
    (lMouseHook, nCode, wParam, ByVal lParam)
 
End Function
 
Private Function GetAppInstance() As Long
 
    GetAppInstance = GetWindowLong _
    (FindWindow("XLMAIN", Application.Caption), GWL_HINSTANCE)
 
End Function

And this code goes in the module of the Worksheet where the listbox is embeeded :

Option Explicit

Private Sub ListBox1_MouseMove _
(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal x As Single, ByVal y As Single)

    Call HookListBox(Me.ListBox1)

End Sub

Tested on Win XP excel 2003.

Regards.