VBA ComboBox List by Criteria

Question

I am trying to create a list using a Userform ComboBox. The list will bring data from the spreadsheet, which can be updated and pushed back to the Spreadsheet. However, I am lost when it comes to creating a list by criteria.

I have Range (A:A), which has the task title. In Range (D:D), I have the status of the task (In Progress, For Check, Approved and Published). The list(s) I am trying to create is to list the Task Title where D=For Check.

Could you please point me in the right direction to be able to write this in VBA.

Answer 1

For example you could add the following code to the userform

Option Explicit


Private Sub UserForm_Initialize()

    ' I assume the list is on the Activesheet
    ' and it has a header row
    Dim rg As Range
    Set rg = Range("A1").CurrentRegion
    Set rg = rg.Offset(1).Resize(rg.Rows.Count - 1)

    Dim vDat As Variant

    ' Goto Tools/References and check Microsoft Scripting Runtime
    Dim rDict As Scripting.Dictionary
    Set rDict = New Scripting.Dictionary

    vDat = rg.Value2

    Dim i As Long
    For i = LBound(vDat) To UBound(vDat)
        ' If column D contains "For Check"
        ' add the task from column A to the dictionary
        If vDat(i, 4) = "For Check" Then
            rDict(vDat(i, 1)) = vDat(i, 1)
        End If
    Next i

    ' I assume the name of the combobox is combobox1
    ComboBox1.List = rDict.Keys
End Sub