• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

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?
0
pziemer
Asked:
pziemer
  • 3
1 Solution
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now