excel用户窗体代码多个组合框

Question

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

我可以复制和粘贴代码10次，然后查找并替换相关的Combobox Nos

有没有办法通过组合框来实现这个循环？ 任何援助将不胜感激。

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 

Answer 1

可以使用类模块，和一个User_Init子设置在用户表格给这个类中的每个框控件。

在我的代码中，我使用了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的名字是主要表格） ：

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 

Answer 2

的方法是使用

类

添加类模块，并给它命名“CmbBox”（你可以选择任何名称，但与它保持一致）

添加以下代码到类代码窗格：

Option Explicit 

Public WithEvents Cmb As MSForms.ComboBox 

Private Sub Cmb_Change() 
    Dim index As Long 

    With Cmb 
     index = .ListIndex 
     With .Parent.Controls("ComboBox" & Mid(.Name, 9) + 10) 
      .Clear 
      Select Case index 
       Case 0 
        .RowSource = Range("SubCat1").Address(external:=True) 
       Case 1 To 4 
        .RowSource = Range("SubCat" & index + 5).Address(external:=True) 
      End Select 
     End With 
    End With 
End Sub 

然后切换到您的userfom代码窗格和添加以下代码：

Dim Cmbs(1 To 10) As New CmbBox '<--| this must be at the very top of your userform code pane 

Sub Userform_Initialize() 
    Dim i As Long 

    With Me.Controls 
     For i = 11 To 20 
      Set Cmbs(i - 10).Cmb = .Item("ComboBox" & i) 
     Next i 
    End With 
End Sub 

，这就是它