CODEWITHSUNDEEP
ms-access
MikH
MikH

Reputation: 3

create vba code in ms access to effect each combo box on a form globally instead of creating code for each individual cbo box.

I have a form in ms access with many combo boxes. I want to see the dropdown options when the cb has the focus. I can do this individually using me.cboboxname.dropdown. instead of doing this individually on all cbo boxes can i create a module or code that will do this for each cbo box each time it has focus?

Upvotes: 0

Views: 258

Answers (1)

Erik A
Erik A

Reputation: 32682

Using classes, you can assign event handlers to all your combo boxes in a single form.

In a separate class module:

Class name: Class1

Public WithEvents cmb As Access.ComboBox

Private Sub cmb_GotFocus()
    cmb.DropDown
End Sub

In the form class module:

Private collEventHandlers As Collection

Private Sub Form_Load()
    Set collEventHandlers = New Collection
    Dim ctl As Access.Control
    Dim eventHandler As Class1
    For Each ctl In Me.Controls
        If TypeOf ctl Is Access.ComboBox Then
            Set eventHandler = New Class1
            Set eventHandler.cmb = ctl
            colleventHanlers.Add eventHandler
            ctl.OnGotFocus = "[Event Procedure]"
        End If
    Next
End Sub

Upvotes: 2

Related Questions