vba - 代码适用于一个组合框，但如何才能适用于多个组合框

Question

我是 VBA 新手，一直在使用一段代码来排序、删除重复项以及从工作表上的特定范围填充组合框。我的问题是，我需要添加哪些内容才能从不同的列填充另一个组合框并仍然对其进行排序。

我正在使用的代码如下。如您所见，我目前正在使用从 B4 开始的信息填充 cboTask。我想添加另一个范围来填充另一个组合框，这将是 cboEquipment，其信息从 D4 开始。

Dim Cell                As Range
Dim Col                 As Variant
Dim Descending          As Boolean
Dim Entries             As Collection
Dim Items               As Variant
Dim index               As Long
Dim j                   As Long
Dim RngBeg              As Range
Dim RngEnd              As Range
Dim row                 As Long
Dim Sorted              As Boolean
Dim temp                As Variant
Dim test                As Variant
Dim Wks                 As Worksheet

Set Wks = ThisWorkbook.Worksheets("Maintenance")

Set RngBeg = Wks.Range("b4")

Col = RngBeg.Column

Set RngEnd = Wks.Cells(Rows.Count, Col).End(xlUp)

    Set Entries = New Collection
    ReDim Items(0)

    For row = RngBeg.row To RngEnd.row
        Set Cell = Wks.Cells(row, Col)
            On Error Resume Next
                test = Entries(Cell.Text)
                If Err = 5 Then
                    Entries.Add index, Cell.Text
                    Items(index) = Cell.Text
                    index = index + 1
                    ReDim Preserve Items(index)
                End If
            On Error GoTo 0
    Next row

    index = index - 1
    Descending = False

    ReDim Preserve Items(index)

        Do
            Sorted = True

            For j = 0 To index - 1
                If Descending Xor StrComp(Items(j), Items(j + 1), vbTextCompare) = 1 Then
                    temp = Items(j + 1)
                    Items(j + 1) = Items(j)
                    Items(j) = temp

                    Sorted = False
                End If
            Next j

            index = index - 1

        Loop Until Sorted Or index < 1


    cboTask.List = Items

提前谢谢您，我以为这就像复制代码并更改暗淡值一样简单，但它似乎不起作用。

Answer 1

将主代码移至具有两个参数的子代码中，并在每个组合框和范围上调用它:

With ThisWorkbook.Worksheets("Maintenance")
    FillComboFromRange cboTask, .Range("B4")
    FillComboFromRange cboOtherOne, .Range("C4")
End With

子填充组合框:

Sub FillComboFromRange(cbo As msforms.ComboBox, RngBeg As Range)

    '...
    '...fill your Items array starting from RngBeg
    '...

    cbo.List = Items '<< assign to combo

End Sub