MrExcel Publishing
Your One Stop for Excel Tips & Solutions

API - "SendMessage" - in Excel


Posted by Robb on February 07, 2002 10:33 AM

Does anyone know how to use the "SendMessage" API call in Excel? It doesn't recognize the method "ByVal hwnd as Long" when applied to an object (combobox for example). Thanx.


Posted by Mark O'Brien on February 07, 2002 12:08 PM

Could you post a little bit of your code so I can see how you are trying to use this. It sounds like you haven't done anything to get the handle of the combobox, this could be reason it's not working. You'd have to use something like the API calls FindWindow and FindWindowEx for this.

Posted by Robb on February 07, 2002 1:57 PM

As requested:

'Following code is in VBA Module
Option Explicit

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long

Public Const CB_FINDSTRING = &H14C

'Following Code is in a form
Private Sub cboStartOrder_Change()
Dim iStart As Integer
Dim sString As String
Static iLeftOff As Integer

iStart = 1
iStart = cboStartOrder.SelStart

If iLeftOff <> 0 Then
cboStartOrder.SelStart = iLeftOff
iStart = iLeftOff
End If

sString = CStr(Left(cboStartOrder.Text, iStart))
cboStartOrder.ListIndex = SendMessage(cboStartOrder.hwnd, _
CB_FINDSTRING, -1, ByVal CStr(Left( _
cboStartOrder.Text, iStart)))

If cboStartOrder.ListIndex = -1 Then
iLeftOff = Len(sString)
cboStartOrder.Text = sString
End If

cboStartOrder.SelStart = iStart
iLeftOff = 0
End Sub

Posted by Mark O'Brien on February 07, 2002 2:59 PM

Quickly ran your code and the problem is "cboStartOrder.hwnd". The reason being that you can't get a handle for the combobox this way in VBA. I'm going home now and I'll have a mess around with getting that handle using FindWindow and FindWindowEx.

Posted by Mark O'Brien on February 07, 2002 6:32 PM

I've taken your code and added one API call, FindWindowEx. This finds the correct combobox for me, it may be necessary to move the cboStartOrder to the front of the ZOrder using something like:

cboStartOrder.ZOrder 0

Anyway, hope this works, just stick the new API call in your API module.

'Following code is in VBA Module
Option Explicit

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long

Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Public Const CB_FINDSTRING = &H14C

'Following Code is in a form
Private Sub cboStartOrder_Change()
Dim hwndCbo As Long
Dim iStart As Integer
Dim sString As String

Static iLeftOff As Integer


'Get Handle for cboStartOrder
hwndCbo = FindWindowEx(0, 0, "ComboLBox", vbNullString)

iStart = 1
iStart = cboStartOrder.SelStart


If iLeftOff <> 0 Then
cboStartOrder.SelStart = iLeftOff
iStart = iLeftOff
End If

sString = CStr(Left(cboStartOrder.Text, iStart))
cboStartOrder.ListIndex = SendMessage(hwndCbo, _
CB_FINDSTRING, -1, ByVal CStr(Left( _
cboStartOrder.Text, iStart)))

If cboStartOrder.ListIndex = -1 Then
iLeftOff = Len(sString)
cboStartOrder.Text = sString
End If

cboStartOrder.SelStart = iStart
iLeftOff = 0
End Sub