VBA (Not VB) -- How to expand comboBox dropdown when the editable area gets focus

I have a comboBox on an excel spreadsheet, and I want to open (expand) the dropdown list when the user clicks in the editable area.  I have the following code, but it doesn't work:

Private Sub Wordbox_GotFocus()
      Dim retv as long
      retv = SendMessageLong(Wordbox.hwnd, CB_SHOWDROPDOWN, True, 0)
End Sub

This code fails because VBA doesn't have a hwnd property for combo boxes, so the first parameter causes an error.

I tried to get the comboBox's handle like this:

Dim TheHandle As Long
TheHandle = FindWindow("ComboBox", "Wordbox")

But the FindWindow API returned zero. (Probably I didn't send the right parameter values, but I don't know what to send.

(Yes, I have the API declarations -- omitted for brevity.)

Can anyone tell me how to open the list when a VBA combobox gets focus?
pziemerAsked:
Who is Participating?
 
ComputronCommented:
or use SENDKEYS "% {DOWN}" or SENDKEYS "{F4}"
0
 
pziemerAuthor Commented:
Nice, clean solution -- and no APIs.

My combobox, named "Wordbox",  is on a worksheet.  Here's the working code.

Private Sub Wordbox_GotFocus()
    Wordbox.DropDown
End Sub

Thanks.
0
 
ComputronCommented:
They must have just added that in 2K3, I hadn't seen it before either.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.