Opposite of combo box .Dropdown method?

Asked 12/1, 2012 at 18:18 Answered 1/6, 2022 at 22:59

Is there a way to toggle the dropdown of a combo box through VBA? .Dropdown is a method and it only works in one direction. I'm looking for the following functionality:

MyCombo.Dropdown = True
MyCombo.Dropdown = False

Obviously the above does not work in MS Access. I'm guessing I'll need to use some sort of hack/workaround for this. I'd like to avoid a .Requery. That would probably work, but there could be a major performance penalty depending on what the source of the combo box is.

Amaya answered 12/1, 2012 at 18:18 Comment(6)

In your reply to Tim, you indicated you want this to work without another control which can receive focus. Then how will you drive (the equivalent of) Dropdown = False? With a keyboard shortcut? Then why not just ESC? Sorry, but I think I missed something here. – Wamsley 12/1, 2012 at 20:32

Do you know that F4 toggles the dropdown? – Rebut 12/1, 2012 at 20:45

I suppose a SendKeys "{F4}" would be one way to toggle the dropdown from code. Of course, the F4 could have other side effects, but it is certainly a reasonable approach. – Amaya 12/1, 2012 at 22:10

SendKeys is disallowed in Vista AFAIK, so you cannot rely on it. – Rebut 12/1, 2012 at 22:12

@HansUp: I'm trying to increase the "mouse target area" for users when choosing options from a combo box with relatively few options. The goal is that instead of having to hit the box with the triangle on the right side of the combo box, they could single click anywhere inside the combo box and it would drop down the list of options. Another single-click would then toggle the combo box closed. Make sense? – Amaya 12/1, 2012 at 22:12

Yes, thanks, that helped. But I fear I'm still not on the same page. To increase the target area, open the combo's dropdown on GotFocus. Then moving to the next control will close the dropdown. If MyCombo is the only control on the form, just leave the dropdown open. (My suspicion is that isn't satisfactory, but I don't understand why.) – Wamsley 12/1, 2012 at 23:34

I was dealing this this issue today, and after a lot of trial and error, I discovered that the following works beautifully in Access 2010:

With m_cboLookup
    .ListWidth = .ListWidth    ' Close combo box
    'If Len(strSearch) > 3 Then .Dropdown
End With

Essentially, you are just setting the list width to the existing list width, but the combo box is closed, presumably to prepare for redrawing at a different size.

Ratepayer answered 28/3, 2019 at 21:22 Comment(4)

Brilliantly kludgey! Does exactly what I was looking for with no performance penalty that I can see. – Amaya 29/3, 2019 at 14:52

@cyberponk - Yes, this question was about a Microsoft Access form control, which is totally different from the UserForm objects and controls in Excel. You may have to find a different approach for your project. – Ratepayer 10/6, 2019 at 15:38

@AdamsTips: Excellent discovery! But one caveat (for Word VBA, at least): if, while the ComboBox still has focus, the VBA code runs a Find operation (to validate the user’s item-selection, in my case), then the ComboBox will be re-opened by the Find object’s Execute method call, for reasons that I can’t fathom. Furthermore, any subsequent setting of .ListWidth = .ListWidth will not close the ComboBox! – Malposition 14/6, 2023 at 17:23

Maybe when a Find operation is executed while the ComboBox has focus, the Find object automatically "restores" focus to the ComboBox, which then opens it. But why .ListWidth = .ListWidth would then subsequently fail to re-close the ComboBox, I have no idea. Yet another Microsoft head-scratcher. – Malposition 14/6, 2023 at 17:24

How about setting the focus to another control on the form? That should close the combo box just as if the user had moved the focus somewhere else on the form.

Intermediacy answered 12/1, 2012 at 18:28 Comment(2)

That's a definite possibility. It would require at least one other focusable control on the form. MyCombo.Dropdown = False becomes MyOtherCtl.SetFocus: MyCombo.SetFocus – Amaya 12/1, 2012 at 19:34

I'd like to avoid having the req't for another focusable control because I am trying to generalize this as much as possible. If I don't get any other ideas, though, this would work. +1 – Amaya 12/1, 2012 at 19:36

I was just dealing with this as well. The best I could come up with is below. It sends the ALT key twice, which closes the combobox without triggering an Undo or moving focus to another control.

SendKeys "%"
SendKeys "%"

Bucko answered 30/7, 2018 at 18:51 Comment(1)

Works on Excel 2019 userform! – Jehanna 10/6, 2019 at 23:6

Have you thought about a

SendKeys "{TAB}"

doesn't require you to send focus on any particular control but moves focus off this one

Templas answered 12/1, 2012 at 22:8 Comment(3)

One potential problem with that is if this is the last control in the tab order. In that case, it would attempt an immediate save of the record and move to the next one. I think that would be unsettling to the user. Otherwise, a great suggestion. Thanks. – Amaya 12/1, 2012 at 22:27

If you place a "save record" button on the form that is at the end of the tab order then, if nothing else, it will receive the focus. – Templas 13/1, 2012 at 1:33

Why would you want a save record button? The default in Access is to save the record, so a save record button is often misleading. – Rebut 13/1, 2012 at 10:34

I had tried everything to achieve my desired combo box behavior. I finally found a method that works for me. It's way too elaborate to be the ideal solution, but it works. I tried Adam's listwidth reset method, but it didn't work for me in Access 2013. I had tried the Sendkeys method, but that caused my clients' Num Lock to be turned off. This code gives me perfect combo box behavior.

'The following code goes in a non-class module.
Public booListOpen As Boolean

Public sub subDropDown()

    If booListOpen = False Then
        Screen.ActiveControl.Dropdown
        booListOpen = True
    End If

End Sub

'The following code goes in the form module.
Private Sub cboList_Enter()

    booListOpen = False

End Sub

Private Sub cboList_Change()

    subDropDown

End Sub

Private Sub cboList_Click()

    booListOpen = True

End Sub

Radiothermy answered 8/4, 2019 at 17:22 Comment(0)

I know this is an old thread but I had the same problem and tried several solutions. The .ListWidth didn't work for me. It did close the dropdown, but it displayed the bound column values (the IDs) in the 'text box' part of the combobox after the dropdown was closed. Mine is also a multi-select combobox bound to a multi-value field; may be different that the OP.

I was able to solve it by doing .Requery

Lacerated answered 28/5, 2020 at 20:54 Comment(0)

worked for me.
Combo on worksheet.
SendKeys "{ESC}"
SendKeys "%{Down}"

Lactoflavin answered 1/6, 2022 at 22:59 Comment(1)

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Ashford 3/6, 2022 at 15:25

Recommended topics

Hot tags