ComboBox to filter Listbox Data

[Tommy Murphy]

Hi 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:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">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</code>

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

 

[Kenneth Hobson]

There are several ways to do it. Since you can make that into an array, using Filter() might be the first route to consider.

 

[Tommy Murphy]

There are several ways to do it. Since you can make that into an array, using Filter() might be the first route to consider.

HI Kenneth

Thanks for your reply.
Unfortunately I have reached the end of my vba knowhow and do not know how to do what you have suggested.

Could you please point me in the right direction?

Thanks

 

[My Aswer Is This]

Here is just one little bit of code you could use to load your 6 Comboboxes
It's just another way in case you want to know.

Private Sub UserForm_Initialize()
'Modified  12/6/2018  12:17:18 PM  EST
Dim ctrl As Control
Dim i As Long
    For i = 1 To 6
    
        With Me.Controls("Combobox" & i)
            .AddItem "COMPLETED"
            .AddItem "HOLIDAY"
            .AddItem "SICKNESS"
            .AddItem "N/A"
        End With
    Next
End Sub

 

[Tommy Murphy]

Here is just one little bit of code you could use to load your 6 Comboboxes
It's just another way in case you want to know.

Private Sub UserForm_Initialize()
'Modified  12/6/2018  12:17:18 PM  EST
Dim ctrl As Control
Dim i As Long
    For i = 1 To 6
    
    With Me.Controls("Combobox" & i)
        .AddItem "COMPLETED"
        .AddItem "HOLIDAY"
        .AddItem "SICKNESS"
        .AddItem "N/A"
    End With
Next
End Sub

HI, I shall certainly give that a try as it looks much cleaner.

As I said in my original post, I am very much a newbie but am learning fast!

Thanks

 

[My Aswer Is This]

Why do you load all 6 of your comboboxes on initialize but then have this:

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)
​

 

[Kyle123]

Xposted here https://www.excelforum.com/excel-programming-vba-macros/1255604-combobox-to-filter-listbox-data.html

 

[Tommy Murphy]

Why do you load all 6 of your comboboxes on initialize but then have this:

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)
​

Hi. The Combobox's are populated with the possible choices on initialize, and are then populated with the selection from the listbox so that each item can be edited.

 

[Tommy Murphy]

I managed to get this working, for anyone with the same issue, this is how I did it:

Private Sub ComboBox7_Change()

'Populate Listbox
Dim Workbk As Workbook
Set Workbk = ThisWorkbook
Dim rng As Range
Dim i As Long, j As Long, rw As Long
Dim Myarray() As String


Set rng = Workbk.Worksheets("Ops Data").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
        If i = 1 Or rng.Cells(i, 2).Value Like Me.ComboBox7.Value & "*" Then
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            Next j
            rw = rw + 1
        End If
    Next i


    .List = Myarray
    '.TopIndex = 1
End With


If Val(Me.txtLBSelectionIndex) > 1 Then
    Me.ListOfData.Selected(Val(Me.txtLBSelectionIndex)) = True
End If
    
End Sub[CODE]