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.

NoLiver92 · Accepted Answer · 2013-03-22 11:36:25Z

5

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.

answered Mar 22, 2013 at 11:36

NoLiver92

8943 gold badges15 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex K. · Accepted Answer · 2013-03-22 11:35:28Z

4

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

answered Mar 22, 2013 at 11:35

Alex K.

177k32 gold badges276 silver badges299 bronze badges

1 Comment

NoLiver92 Over a year ago

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

K_B · Accepted Answer · 2013-03-22 10:43:30Z

2

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

answered Mar 22, 2013 at 10:43

K_B

3,6781 gold badge21 silver badges29 bronze badges

2 Comments

NoLiver92 Over a year ago

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 Over a year ago

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

Cool Blue · Accepted Answer · 2013-12-31 00:59:41Z

Is there a reason you don't just add a property to your custom class and set that property when you register in the Collection?

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
'*******EDIT********
    with cButton
        .combobox = combo
        .Indx = j
    end With    'cButton
'*******************
    coll.Add cButton
End With
Height = Height + 30
Next j

Class Module

Public WithEvents btn As MSForms.CommandButton
Dim WithEvents mCombobox As MSForms.comboBox
Private combolist() As String

'*******EDIT********
Public Indx As Long

Property Let comboBox(cb As MSForms.comboBox)
    Set mCombobox = cb
End Property
'*******************

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 mCombobox_Click()

'*******EDIT********
    MsgBox "Combobox " & Indx & Chr(9) & mComboBox.Value
'*******************

End Sub

Since you require many to one mapping of the events, I assume you have a common call-back in your actual code, so you could also do this...

In a Standard Module

Public Sub cbCallBack(ocb As clsButton)
    MsgBox ocb.Indx
End Sub

In clsButton (replacing the event handler)

Private Sub mCombobox_Click()
    cbCallBack Me
End Sub

Collectives™ on Stack Overflow

Excel VBA ComboBox Identification

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related