Search function for listbox userform

[Megg]

I'm trying to create a search function in my userform that's connected to the listbox. I have three textbox options to search from - search via surname is Textbox1, first name is Textbox2 and by year is Textbox3. I've managed to get it to kind of work, however when I add the else if's for the other two textboxes it glitches. I want to be able to search individually, not having to put an input for every textbox.
I would also like to have it so that it just shows only the search results instead of having the results moved to the top if possible?
This is what I have so far:
Userform:

Private Sub UserForm_Initialize()
    
  Set tblbreakdownTable = ThisWorkbook.Worksheets("Breakdown").ListObjects("breakdownTable")
 
  With playerList
    .List = tblbreakdownTable.DataBodyRange.Value2
    .ColumnCount = 9
    .ColumnHeads = False
    
End With
  
End Sub

Search Button:

Private Sub SearchButton_Click()
Sur2 = TextBox1.value
Fnam2 = TextBox2.value
Year2 = TextBox3.value

For sat = 2 To Cells(rows.Count, 1).End(xlUp).Row
Set Sur1 = Cells(sat, "A")
Set Fnam1 = Cells(sat, "B")
Set Year1 = Cells(sat, "C")

If Sur1 Like Sur2 & "*" Then
    playerList.AddItem
    playerList.List(s, 0) = Cells(sat, "A")
    playerList.List(s, 1) = Cells(sat, "B")
    playerList.List(s, 2) = Cells(sat, "C")
    playerList.List(s, 3) = Cells(sat, "D")
    playerList.List(s, 4) = Cells(sat, "E")
    playerList.List(s, 5) = Cells(sat, "F")
    playerList.List(s, 6) = Cells(sat, "G")
    playerList.List(s, 7) = Cells(sat, "H")
    playerList.List(s, 8) = Cells(sat, "I")
    s = s + 1
ElseIf Fnam1 Like Fnam2 & "*" Then
    playerList.AddItem
    playerList.List(s, 0) = Cells(sat, "A")
    playerList.List(s, 1) = Cells(sat, "B")
    playerList.List(s, 2) = Cells(sat, "C")
    playerList.List(s, 3) = Cells(sat, "D")
    playerList.List(s, 4) = Cells(sat, "E")
    playerList.List(s, 5) = Cells(sat, "F")
    playerList.List(s, 6) = Cells(sat, "G")
    playerList.List(s, 7) = Cells(sat, "H")
    playerList.List(s, 8) = Cells(sat, "I")
    s = s + 1
ElseIf Year1 Like Year2 & "*" Then
    playerList.AddItem
    playerList.List(s, 0) = Cells(sat, "A")
    playerList.List(s, 1) = Cells(sat, "B")
    playerList.List(s, 2) = Cells(sat, "C")
    playerList.List(s, 3) = Cells(sat, "D")
    playerList.List(s, 4) = Cells(sat, "E")
    playerList.List(s, 5) = Cells(sat, "F")
    playerList.List(s, 6) = Cells(sat, "G")
    playerList.List(s, 7) = Cells(sat, "H")
    playerList.List(s, 8) = Cells(sat, "I")
    s = s + 1


End If: Next

End Sub

I have everything else working in the userform such as edit, delete and add to listbox. Just need the search function to make things easier. I'm still pretty new to Excel VBA so any assistance is appreciated. Thank you!

 

[DanteAmor]

Hi @Megg

I propose the following. Instead of using the search button. We are going to use the change event of each textbox, so as you capture in any of the 3 textboxes, the filter will be done automatically.
Additionally, you can capture in any of the 3 textboxes or filter with 2 textboxes or filter with all 3 textboxes at the same time.

Replace your code with the following:

Dim a As Variant                  '<<---At the beginning of all the code

Private Sub TextBox1_Change()
  Call filterdata
End Sub
Private Sub TextBox2_Change()
  Call filterdata
End Sub
Private Sub TextBox3_Change()
  Call filterdata
End Sub

Sub filterdata()
  Dim i As Long, j As Long, k As Long
  Dim txt1 As String, txt2 As String, txt3 As String
  
  With playerList
    .Clear
    For i = 2 To UBound(a)
      If TextBox1.Value = "" Then txt1 = a(i, 1) Else txt1 = TextBox1.Value & "*"
      If TextBox2.Value = "" Then txt2 = a(i, 2) Else txt2 = TextBox2.Value & "*"
      If TextBox3.Value = "" Then txt3 = a(i, 3) Else txt3 = TextBox3.Value & "*"
      
      If LCase(a(i, 1)) Like LCase(txt1) And LCase(a(i, 2)) Like LCase(txt2) And _
         LCase(a(i, 3)) Like LCase(txt3) Then
        .AddItem
        For j = 1 To 9
          .List(k, j - 1) = a(i, j)
        Next
        k = k + 1
      End If
    Next
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim tblbreakdownTable As ListObject
  Set tblbreakdownTable = ThisWorkbook.Worksheets("Breakdown").ListObjects("breakdownTable")
 
  With playerList
    a = tblbreakdownTable.DataBodyRange.Value2
    .List = a
    .ColumnCount = 9
    .ColumnHeads = False
  End With
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Megg]

Thank you so much for that @DanteAmor ! There's one error that's popping up though. It says - "Run-time error '13'. Type mismatch". And it's highlighted For i = 2 To UBound(a)
Not really sure what that means? I have placed the Dim a As Variant at the top of my code as instructed.

 

[DanteAmor]

Did you update the code for the UserForm_Initialize() event?

With playerList
    a = tblbreakdownTable.DataBodyRange.Value2
    .List = a
    .ColumnCount = 9
    .ColumnHeads = False
  End With

By the way, must be:
For i = 1 To UBound(a)


try and tell me

 

[Megg]

Oh woops, so sorry I completely misread and missed that. The search function is working great now! However it seems to have broken my other buttons for example my edit button.
Thank you so much for the help with the search function. Any more assistance would be greatly appreciated.

On the search form in regards to the edit button for example I have.

Dim tblbreakdownTable As ListObject
Public RecordRow As Long
Dim a As Variant

Private Sub EditButton_Click()
    EditForm.UpdateRecord Me, tblbreakdownTable
End Sub

Then for the edit form I have:

Private Sub cmdUpdate_Click()
    Me.Hide
End Sub

Private Sub cmdCancel_Click()
    cmdCancel.Tag = vbCancel
    Me.Hide
End Sub

Sub UpdateRecord(ByVal Form As Object, ByVal objTable As Object)
    Dim rngData     As Range
    Dim i           As Long
  
    With Form.playerList
        For i = 1 To 7
            Me.Controls("TextBox" & i).text = .Column(Choose(i, 1, 0, 2, 3, 4, 6, 7))
        Next i
        Form.RecordRow = .ListIndex + 1
        
    End With
    
    Me.Show
    
  
    If Not Val(Me.cmdCancel.Tag) = vbCancel Then
      
        For i = 1 To 9
            'post record to table
            With Me.Controls("TextBox" & i)
                objTable.DataBodyRange.Cells(Form.RecordRow, Choose(i, 2, 1, 3, 4, 5, 7, 8, 6, 9)).value = .value
            End With
        Next i
      
        Set rngData = objTable.DataBodyRange
      
        'update listbox
        With Form.playerList
            .Clear
            .List = rngData.Value2:  .ListIndex = Form.RecordRow - 1
        End With
        'inform user
        MsgBox "Record Updated", 64, "Success"
    End If
  
    Unload Me
  
End Sub

Private Sub TextBox4_Change()
Call Module3.Calculations
End Sub
Private Sub TextBox5_Change()
Call Module3.Calculations
End Sub

Private Sub TextBox6_Change()
Call Module3.Calculations
End Sub

Private Sub TextBox7_Change()
Call Module3.Calculations
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = CloseMode = 0
    If Cancel Then Call cmdCancel_Click
End Sub

 

[DanteAmor]

Hi.

In the code that I put in, I declared the variable tblbreakdownTable, because I didn't know that you had it in the general declarations.

Then delete this line:

Private Sub UserForm_Initialize()
  Dim tblbreakdownTable As ListObject
  Set tblbreakdownTable = ThisWorkbook.Worksheets("Breakdown").ListObjects("breakdownTable")
 
  With playerList
    a = tblbreakdownTable.DataBodyRange.Value2
    .List = a
    .ColumnCount = 9
    .ColumnHeads = False
  End With
End Sub

Warning!!!

Form.RecordRow = .ListIndex + 1

Now, consider the following, if you are filtering the information, and you want to modify a filtered record, the record number in the listbox will not be the same as the record number on the sheet.

I will try to explain it, if in the sheet you have the data "Dave" in rows 4, 6 and 10 and you filter by name "Dav*", then in the listbox you will have 3 records:
in position 0 you will have record 4,
in position 1 you will have record 6 and
in position 2 you will have record 10.

So with this line: Form.RecordRow = .ListIndex + 1

If you want to modify position 2 of the listbox, you would really be modifying record 3 of the sheet. And you need to modify record 10, then how to achieve that. My proposal is to store the record number in an additional column in the listbox. This way, when you select an item from the listbox, you will be able to know which is its corresponding row on the sheet.

Then we must make changes to your code.

1. filterdata:​

Sub filterdata()
  Dim i As Long, j As Long, k As Long
  Dim txt1 As String, txt2 As String, txt3 As String
  
  With playerList
    .Clear
    For i = 1 To UBound(a)
      If TextBox1.Value = "" Then txt1 = a(i, 1) Else txt1 = TextBox1.Value & "*"
      If TextBox2.Value = "" Then txt2 = a(i, 2) Else txt2 = TextBox2.Value & "*"
      If TextBox3.Value = "" Then txt3 = a(i, 3) Else txt3 = TextBox3.Value & "*"
      
      If LCase(a(i, 1)) Like LCase(txt1) And LCase(a(i, 2)) Like LCase(txt2) And _
         LCase(a(i, 3)) Like LCase(txt3) Then
        .AddItem
        For j = 1 To 9
          .List(k, j - 1) = a(i, j)
        Next
        .List(k, 9) = i     'Stores the row number of the sheet.
        k = k + 1
      End If
    Next
  End With
End Sub

2. UserForm_Initialize​

Private Sub UserForm_Initialize()
  Set tblbreakdownTable = ThisWorkbook.Worksheets("Breakdown").ListObjects("breakdownTable")
  
  With playerList
    a = tblbreakdownTable.DataBodyRange.Value2
    .List = a
    .ColumnCount = 9
    .ColumnHeads = False
  End With
End Sub

3. EditButton​

Private Sub EditButton_Click()
  Dim i As Long
  If playerList.ListIndex = -1 Then
    MsgBox "Select item"
    Exit Sub
  End If
  
  EditForm.UpdateRecord Me, tblbreakdownTable
  a = tblbreakdownTable.DataBodyRange.Value2
  For i = 1 To UBound(a)
    playerList.List(i - 1, 9) = i
  Next
End Sub

4. In EditForm. UpdateRecord​

Sub UpdateRecord(ByVal Form As Object, ByVal objTable As Object)
    Dim rngData     As Range
    Dim i           As Long
  
    With Form.playerList
        For i = 1 To 7
            Me.Controls("TextBox" & i).Text = .Column(Choose(i, 1, 0, 2, 3, 4, 6, 7))
        Next i
        'Form.RecordRow = .ListIndex + 1
        Form.RecordRow = .List(.ListIndex, 9) 'Retrieves the row number of the sheet
    End With
    Me.Show
  
    If Not Val(Me.cmdCancel.Tag) = vbCancel Then
      
        For i = 1 To 9
            'post record to table
            With Me.Controls("TextBox" & i)
                objTable.DataBodyRange.Cells(Form.RecordRow, Choose(i, 2, 1, 3, 4, 5, 7, 8, 6, 9)).Value = .Value
            End With
        Next i
      
        Set rngData = objTable.DataBodyRange
        'update listbox
        With Form.playerList
            .Clear
            .List = rngData.Value2
            .ListIndex = Form.RecordRow - 1
        End With
        'inform user
        MsgBox "Record Updated", 64, "Success"
    End If
    Unload Me
End Sub

Note: You will have to do something similar for deleting records when you take it from the filtered records. This happens when you work with filtered records, it is part of the job as a developer


Try again and tell me

Regards
Dante Amor

 

[DanteAmor]

I found a problem, use the following code.

2. UserForm_Initialize

Private Sub UserForm_Initialize()
  Dim i As Long
  Set tblbreakdownTable = ThisWorkbook.Worksheets("Breakdown").ListObjects("breakdownTable")
  
  With playerList
    a = tblbreakdownTable.DataBodyRange.Value2
    .List = a
    For i = 1 To UBound(a)
      playerList.List(i - 1, 9) = i
    Next

    .ColumnCount = 9
    .ColumnHeads = False
  End With
End Sub

Regards
Dante Amor

 

[Megg]

That makes a lot of sense on why it didn't work, thank you for explaining that to me in depth. I made sure I copied everything over correctly, however when i run the SearchForm I instantly get the error 'Run-Time error 380 - Could not set the List property. Invalid property value. ?
Thank you for being a massive help!

 

[DanteAmor]

It's hard to know if you copied the code properly from this point, especially if you don't share the final code you have.

So I'm going to choose to share my tests file so you can see the code working.

edit listbox.xlsm

drive.google.com

Regards
Dante Amor

 

[Megg]

Thank you for that! I did copy over everything correctly, however after trial and erroring I realised some numbers were wrong for my spreadsheet. Got it all working now. I really appreciate all the help!