代码之家 › 专栏 › 技术社区 › NoLiver92

Excel VBA组合框标识

combobox excel vba

NoLiver92 · 技术社区 · 12 年前

我在一个用户窗体上有4个以上的组合框。当它们触发时,它们会触发相同的事件。我想做的是找出是哪个组合框触发了事件。组合框的创建取决于组件的数量。生成组合框的代码如下所示:

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

这很有效,我可以得到用户选择的值,但我找不到使用了哪个组合框。下面的代码是它触发的事件( 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

上面的这段代码是Doug Glancy精心编写的,目的是让事件与代码生成的ComboBoxes一起工作。

如何获取触发事件的组合框?即名称或某种其他形式的标识。

4 回复 | 直到 12 年前

NoLiver92 12 年前

在搜索了500多个网页后,我终于回答了自己的问题(花了很长时间)

这就是我使用的方法,当单击某些组合框时,它会工作并启动:

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

希望这可以用于其他需要它的人。

Alex K. 12 年前

在班级内 .Name 不会显示在组合框的intellisense列表中 MSForms.ComboBox 实际上本身并没有名称属性(请在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

K_B 12 年前

也许再次引用btn.Combobox?类似于最初将组合框分配给按钮的方式,但后来又反过来:

set combobox = btn.Combobox

Cool Blue 12 年前

当您在集合中注册时,是否有理由不将属性添加到自定义类中并设置该属性?

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

课堂模块

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

由于您需要事件的多对一映射,我假设您在实际代码中有一个常见的回调,所以您也可以这样做。。。

在标准模块中

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

在clsButton中(替换事件处理程序)

Private Sub mCombobox_Click()
    cbCallBack Me
End Sub