Excel VBA ComboBox Identification

combobox, excel, vba

Solution

Within the class `.Name` will not appear in the intellisense list for the combobox as `MSForms.ComboBox` does not actually have a name property itself (take a look at it in the F2 object browser), rather that property is provided by the `Control` base class:

Private Sub combobox_Click()

    MsgBox combobox.Value
    MsgBox combobox.Name '// no hint but still works

    '//cast to a Control to get the formal control interface with .Name
    Dim ctrl As Control: Set ctrl = combobox
    MsgBox ctrl.Name

End Sub

Problem

I have 4+ ComboBoxes on a user form. When they fire, they fire the same event. What I am trying to do is find out which ComboBox triggered the event. The ComboBoxes are created depending on how many components there are. The code generating the ComboBoxes is shown below: ``` For j = 0 To UBound(ComponentList) - 1 'Set Label num = j + 1 Set control = UserForm1.Controls.Add("Forms.Label.1", "ComponentLabel" & CStr(num) & ":", True) With control .Caption = "Component " & CStr(num) .Left = 30 .Top = Height .Height = 20 .Width = 100 .Visible = True End With 'set ComboBox Set combo = UserForm1.Controls.Add("Forms.ComboBox.1", "Component" & num & ":", True) With combo .List = ComponentList() .Left = 150 .Top = Height .Height = 20 .Width = 50 .Visible = True Set cButton = New clsButton Set cButton.combobox = combo coll.Add cButton End With Height = Height + 30 Next j ``` This works well and I can get the value the user selected, BUT I can not find which ComboBox has been used. This code below is the event that it fires (`clsButton`): ``` Public WithEvents btn As MSForms.CommandButton Public WithEvents combobox As MSForms.combobox Private combolist() As String Private Sub btn_Click() If btn.Caption = "Cancel" Then MsgBox "Cancel" Unload UserForm1 Variables.ComponentSelectionError = False ElseIf btn.Caption = "Enter" Then MsgBox "enter" Unload UserForm1 Variables.ComponentSelectionError = True End If End Sub Private Sub combobox_Click() MsgBox combobox.Value End Sub ``` This bit of code above was kindly worked on by Doug Glancy to get the events working with the code generated ComboBoxes. How do I get the ComboBox that triggered the event? i.e. the name or some other form of identification.

Original source