在excel用户窗体上编码多个组合框

debugcn 发表于 Dev

威利

我有一个带有多个从属组合框的用户窗体。我想将以下代码添加到Comboboxes Change事件的10个中。要编码的组合框编号为11到20（组合框11，组合框12等），而从属组合框的编号为21到30。

我可以将代码复制并粘贴10次，然后找到并替换相关的组合框编号。

有没有办法通过组合框使用循环来实现这一目标？任何帮助将不胜感激。

Private Sub ComboBox11_Change()

Dim index As Integer
index = ComboBox11.ListIndex
ComboBox21.Clear

Select Case index
    Case Is = 0
        With ComboBox21
            .RowSource = Range("SubCat1").Address(external:=True)
        End With

    Case Is = 1
        With ComboBox21
            .RowSource = Range("SubCat6").Address(external:=True)
        End With

    Case Is = 2
        With ComboBox21
            .RowSource = Range("SubCat7").Address(external:=True)
        End With

    Case Is = 3
        With ComboBox21
            .RowSource = Range("SubCat8").Address(external:=True)
        End With

    Case Is = 4
        With ComboBox21
            .RowSource = Range("SubCat9").Address(external:=True)
        End With

    'and several more case options

End Select

End Sub

Shai Rado

您可以使用类模块和User_Init子类来将用户窗体中的每个ComboBox控件设置为此类。

在我的代码中，我使用Main_Form作为User_Form的名称，根据您的User_Form名称修改代码。

添加一个呼叫模块，并在下面的第1类中添加此代码：

Public WithEvents ComboBoxEvents As MSForms.ComboBox

 ' anytime a Change event occurs to any ComboBox, the Sub is triggered
Private Sub ComboBoxEvents_Change()

Dim ComboBox_Index As String
Dim index As Integer

    With ComboBoxEvents
        ' read the index of the ComboBox, as long as the names remain ComboBox1, ComboBox2, ComboBox3, etc...
        ComboBox_Index = Mid(.Name, 9)

        ' run this code if it's ComboBox 11 to 20
        If ComboBox_Index >= 11 And ComboBox_Index <= 20 Then
            index = .ListIndex

            Select Case index
                Case Is = 0
                    With Main_Form.Controls("ComboBox" & ComboBox_Index + 10)
                        .RowSource = Range("SubCat1").Address(external:=True)
                    End With

                Case Is = 1
                    With Main_Form.Controls("ComboBox" & ComboBox_Index + 10)
                        .RowSource = Range("SubCat6").Address(external:=True)
                    End With

                Case Is = 2
                    With Main_Form.Controls("ComboBox" & ComboBox_Index + 10)
                        .RowSource = Range("SubCat7").Address(external:=True)
                    End With

                Case Is = 3
                    With Main_Form.Controls("ComboBox" & ComboBox_Index + 10)
                        .RowSource = Range("SubCat8").Address(external:=True)
                    End With

                Case Is = 4
                    With Main_Form.Controls("ComboBox" & ComboBox_Index + 10)
                        .RowSource = Range("SubCat9").Address(external:=True)
                    End With

                'and several more case options

            End Select                
        End If
    End With

End Sub

下面的代码在您的User_Form_Init中（在我的代码中，User_Form的名称为Main-Form）：

Option Explicit

Dim ComboBoxes() As New Class1

Private Sub UserForm_Initialize()

    Dim ComboBoxCounter As Integer, Obj As Control

    For Each Obj In Me.Controls
        If TypeOf Obj Is MSForms.ComboBox Then
            ComboBoxCounter = ComboBoxCounter + 1
            ReDim Preserve ComboBoxes(1 To ComboBoxCounter)
            Set ComboBoxes(ComboBoxCounter).ComboBoxEvents = Obj
        End If
    Next Obj

    Set Obj = Nothing

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。