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

Posted on 2006-03-22
Last Modified: 2008-01-09
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?
Question by:pziemer
    LVL 15

    Expert Comment

    or use SENDKEYS "% {DOWN}" or SENDKEYS "{F4}"
    LVL 15

    Accepted Solution


    Author Comment

    Nice, clean solution -- and no APIs.

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

    Private Sub Wordbox_GotFocus()
    End Sub

    LVL 15

    Expert Comment

    They must have just added that in 2K3, I hadn't seen it before either.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now