ComboBox to filter Listbox Data

Tommy Murphy

New Member
Joined
Nov 27, 2018
Messages
6
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:
<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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
There are several ways to do it. Since you can make that into an array, using Filter() might be the first route to consider.
 
Upvote 0
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
 
Upvote 0
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.


Code:
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
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
I managed to get this working, for anyone with the same issue, this is how I did it:

Code:
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]
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top