ComboBox to filter Listbox Data

Please note that this question has also been posted here: mrexcel.com/forum/excel-questions/1079976-combobox-filter-listbox-data.html
(Unable to post link due to forum rules)


Good morning all!

I am pretty much a newbie but reasonably adept at adapting code (or bludgeoning my way through until I manage to somehow get things working!)

I am having trouble getting a userform listbox to filter based on combobox selection. To be honest, I dont even know where to begin with making this work..

My Code is as follows:

Option Explicit



Private Sub cmdClose_Click()
Unload UserForm1
End Sub

Private Sub ComboBox7_Change()

End Sub

Private Sub UserForm_Initialize()

    cmdUpdate.Enabled = False 'Only enable the button when a row has been returned
    
    'Combo Lists
    ComboBox1.List = Array("COMPLETED", "HOLIDAY", "SICKNESS", "N/A")
    ComboBox2.List = Array("COMPLETED", "HOLIDAY", "SICKNESS", "N/A")
    ComboBox3.List = Array("COMPLETED", "HOLIDAY", "SICKNESS", "N/A")
    ComboBox4.List = Array("COMPLETED", "HOLIDAY", "SICKNESS", "N/A")
    ComboBox5.List = Array("COMPLETED", "HOLIDAY", "SICKNESS", "N/A")
    ComboBox6.List = Array("COMPLETED", "HOLIDAY", "SICKNESS")
    ComboBox7.List = Worksheets("Data").Range("A2:A50").Value
    
    'Populate Listbox
    Dim rng As Range
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String
    
    Set rng = Range("ListOfData")

    With Me.ListOfData
        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count

        ReDim Myarray(rng.Rows.Count, rng.Columns.Count)

        rw = 0

        For i = 1 To rng.Rows.Count
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            Next
            rw = rw + 1
        Next

        .List = Myarray
        '.TopIndex = 1
        
    End With
    
    If Val(Me.txtLBSelectionIndex) > 1 Then
        Me.ListOfData.Selected(Val(Me.txtLBSelectionIndex)) = True
    End If
  
End Sub
Private Sub cmdSend_Click()

    'NOT YET USED BUT WOULD WORK TO ADD NEW OPERATIVES
    
    'Dim lastrow As Long
    
    'lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    'Cells(lastrow + 1, "A").Value = txtIssue.Text
    'Cells(lastrow + 1, "C").Value = txtDateReceived.Text
    'Cells(lastrow + 1, "D").Value = txtAgency.Text
    'Cells(lastrow + 1, "E").Value = txtService.Text
    'Cells(lastrow + 1, "F").Value = txtSource.Text
    'Cells(lastrow + 1, "G").Value = txtIssueType.Text
    'Cells(lastrow + 1, "H").Value = txtIssueNonIssue.Text
    'Cells(lastrow + 1, "I").Value = txtOwnership.Text
    'Cells(lastrow + 1, "J").Value = txtTimeSpent.Text
    'Cells(lastrow + 1, "K").Value = txtDateCompleted.Text
    'Cells(lastrow + 1, "L").Value = txtActiveDuration.Text
    
End Sub
Private Sub ListOfData_Change()

End Sub
Private Sub ListofData_Click()
    
    Dim rngMyData As Range
    

    txtIssue.Value = Me.ListOfData.Column(0)
    ComboBox1.Value = Me.ListOfData.Column(2)
    ComboBox2.Value = Me.ListOfData.Column(3)
    ComboBox3.Value = Me.ListOfData.Column(4)
    ComboBox4.Value = Me.ListOfData.Column(5)
    ComboBox5.Value = Me.ListOfData.Column(6)
    ComboBox6.Value = Me.ListOfData.Column(7)
    TextBox1.Value = Me.ListOfData.Column(8)
    TextBox2.Value = Me.ListOfData.Column(9)
    txtDateCompleted.Value = Me.ListOfData.Column(10)
    txtActiveDuration.Value = Me.ListOfData.Column(11)
    
    Set rngMyData = Sheets("Sheet1").Columns("A")
    
    On Error Resume Next
        txtRowNumber = Application.WorksheetFunction.Match(txtIssue.Value, rngMyData, 0)
    On Error Resume Next
    
    If Val(txtRowNumber) > 1 Then 'Exclude the ability to change the header row.
        cmdUpdate.Enabled = True 'OK to enable the button as an applicable row number has been returned
    End If
        
End Sub
Private Sub cmdUpdate_Click()

    Dim lngMyRow As Long
    Dim r As Long
    
    lngMyRow = Val(txtRowNumber)
    
    If lngMyRow = 0 Then
        MsgBox "Update is not available as a row number for the selected issue could not be found.", vbExclamation
        Exit Sub
    Else
        Application.EnableEvents = False
            'Return the selected index number of the selected record in the 'ListOfDetails' listbox so it can be re-selected after the list has been refreshed
            For r = 0 To Me.ListOfData.ListCount - 1
                If Me.ListOfData.Selected(r) Then
                    Me.txtLBSelectionIndex = r
                    Exit For
                End If
            Next r
            'Populate the corresponding cells with any change
            Worksheets("Sheet1").Cells(lngMyRow, "A").Value = txtIssue.Text
            Worksheets("Sheet1").Cells(lngMyRow, "C").Value = ComboBox1.Text
            Worksheets("Sheet1").Cells(lngMyRow, "D").Value = ComboBox2.Text
            Worksheets("Sheet1").Cells(lngMyRow, "E").Value = ComboBox3.Text
            Worksheets("Sheet1").Cells(lngMyRow, "F").Value = ComboBox4.Text
            Worksheets("Sheet1").Cells(lngMyRow, "G").Value = ComboBox5.Text
            Worksheets("Sheet1").Cells(lngMyRow, "H").Value = ComboBox6.Text
            'Cells(lngMyRow, "I").Value = TextBox1.Text - Not running on update to stop override of PDR Due Yes/No
            Worksheets("Sheet1").Cells(lngMyRow, "J").Value = Format(TextBox2.Value, "mm/dd/yyyy")
            'Cells(lngMyRow, "K").Value = txtDateCompleted.Text
            'Cells(lngMyRow, "L").Value = txtActiveDuration.Text
        Application.EnableEvents = True
    End If
    
    'Refresh the list
    'Me.ListOfData.RowSource = "ListOfData"
    Call UserForm_Initialize

End Sub

The list box is called: "List of Data"
The Combo I want to use to filter the data is "ComboBox7"
The data for the Listbox is stored on "Sheet1"
Im looking to be able to filter on Row B of the data in sheet 1 (which is Column 2 of the listbox)

Any help would be greatly appreciated!!

If it helps, I can also post the workbook.

Thanks
Tom