Code working for one, but how can I make work for multiple combobox

Viewed 56

I'm new to VBA and have been using a piece of code to sort, remove duplicates and populate a Combobox from a certain range on my worksheet. My question is, what additions do I need to make so that I can populate another Combobox from a different Column and still have it sort.

Code I'm using is as below. As you can see I'm currently filling cboTask with information starting from B4. I want to add another range to fill another Combobox, which would be cboEquipment with information starting at 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

Thank you in advance, I thought this would be as simple as copying the code and changing the dim values, but it doesn't seem to work.

2 Answers
Related