Excel VBA 组合框标识
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15567989/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA ComboBox Identification
提问by NoLiver92
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:
我在用户表单上有 4 个以上的组合框。当它们触发时，它们触发相同的事件。我想要做的是找出哪个 ComboBox 触发了事件。组合框的创建取决于组件的数量。生成组合框的代码如下所示：
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):
这很好用，我可以获得用户选择的值，但我找不到使用了哪个 ComboBox。下面的代码是它触发的事件 ( 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.
上面的这段代码是由 Doug Glancy 善意处理的，以使事件与代码生成的 ComboBox 一起工作。
How do I get the ComboBox that triggered the event? i.e. the name or some other form of identification.
如何获取触发事件的 ComboBox？即姓名或其他某种形式的身明。
采纳答案by Alex K.
Within the class .Namewill not appear in the intellisense list for the combobox as MSForms.ComboBoxdoes not actually have a name property itself (take a look at it in the F2 object browser), rather that property is provided by the Controlbase class:
该类.Name不会出现在组合框的智能感知列表中，因为MSForms.ComboBox它本身实际上没有 name 属性（在 F2 对象浏览器中查看它），而是由Control基类提供该属性：
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
回答by NoLiver92
I have managed to finally answer my own question after searching over 500 webpages (took a long time)
在搜索了 500 多个网页后，我终于设法回答了我自己的问题（花了很长时间）
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.
希望这可以用于其他需要它的人。
回答by K_B
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:
也许再次参考 btn.Combobox ？类似于您首先将组合框分配给按钮的方式，但反过来：
set combobox = btn.Combobox
回答by Cool Blue
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)
在 clsButton 中（替换事件处理程序）
Private Sub mCombobox_Click()
cbCallBack Me
End Sub
