Show dropdown list when combobox has focus

Question

Is it possible to put a single bit of code on an Access form that would automatically show the dropdown list whenever any combobox on the form has focus?

I know I can automatically show the dropdown list whenever a combobox has focus by using the event below. But, if possible, I would prefer to not have to put that line of code on each combobox since I have so many.

Private Sub combobox_GotFocus()
    'When the combobox receives focus
    'display in drop down position
    Me.combobox.Dropdown
End Sub

My database has dozens of forms in it with dozens of comboboxes on each form.

No, there is not a 'single bit of code' that will accomplish this for all comboboxes. Each will need the event code. — June7
Possible duplicate of Creating a Class to Handle Access Form Control Events — Storax
Sorry @Storax but I'm not very good a VBA, I looked at the provided link but I'm not entirely sure where to start. — Kevin Kissell

Storax Storax · Accepted Answer · 2019-01-01T19:25:28

Create a class named cComboBox

Option Compare Database
Option Explicit

Private WithEvents mComboBox As Access.ComboBox

Private Sub mComboBox_GotFocus()
    mComboBox.Dropdown
End Sub

Public Function AddCtl(nCtl As Access.ComboBox) As Access.ComboBox
    Set mComboBox = nCtl
    mComboBox.OnGotFocus = "[Event Procedure]"
    'mComboBox.OnChange = "[Event Procedure]"
    Set AddCtl = mComboBox
End Function

and add the following code to your Form

Option Compare Database
Option Explicit

Dim myCBs As New Collection

Private Sub Form_Load()
Dim myCB As cComboBox

Dim ctl As Access.Control
    For Each ctl In Me.Controls
        If TypeName(ctl) = "ComboBox" Then
            Set myCB = New cComboBox
            myCB.AddCtl ctl
            myCBs.Add myCB
        End If
    Next

End Sub

Show dropdown list when combobox has focus

1 Answers