Excel VBA ComboBox Identification

Question

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.

Solution 1

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

Solution 2

I have managed to finally answer my own question after searching over 500 webpages (took a long time)

this is what i used and it works and fires when the certain comboboxes are clicked:

Private Sub combobox_Click()
MsgBox combobox.Value
If combobox = UserForm1.Controls("Component0") Then
    MsgBox "Success1"
End If
If combobox = UserForm1.Controls("Component1") Then
    MsgBox "Success2"
End If
End Sub

hopefully this can be used for other people who need it.

Solution 3

Maybe reference back to btn.Combobox again? Similar to how you assigned the combobox to the button in the first place, but then in reverse:

set combobox = btn.Combobox 

Comments

[NoLiver92]

im not sure where your coming from with this. but when i do combobox.whatever there is no option for name or any sort of id so im not sure how to do it. i have looked at over 100+ webstes which all tell me how to add and set values but not how to find out which box triggered the event

[K_B]

Ah, your orignal post didnt say you wanted the (Name), just that you wanted to "get the combobox", I therefore thought you wanted to know the object, not that you wanted the name of the object... Thanks for clarifying

[NoLiver92]

Thanks this has worked, i also found a solution after hours of searching and i am now combining them both. thanks for your help!