VBA: search in multiple sheets & display in ListView

[Rnarde]

Hello everyone,

Here is my situation:

I work in a hospital on clinical data for subjects we are following here. We collect 9 data for each subject, which are divided in 9 columns as shown in my UserForm initialization:

Private Sub UserForm_Initialize()

    With ListView1
    
        .Gridlines = True
        .View = lvwReport
        .FullRowSelect = True
        .ColumnHeaders.Add Text:="Builder", Width:=50                 ' column A in all sheets'
        .ColumnHeaders.Add Text:="Type", Width:=50                     ' column B in all sheets'
        .ColumnHeaders.Add Text:="Name", Width:=75                   ' column C in all sheets'
        .ColumnHeaders.Add Text:="Name2", Width:=50                 ' column D in all sheets'
        .ColumnHeaders.Add Text:="FirstName", Width:=50            ' column E in all sheets'
        .ColumnHeaders.Add Text:="Serial number", Width:=130    ' column F in all sheets'
        .ColumnHeaders.Add Text:="Date of birth", Width:=60        ' column G in all sheets'
        .ColumnHeaders.Add Text:="Date of implant", Width:=60   ' column H in all sheets'
        .ColumnHeaders.Add Text:="Date of consent", Width:=60  ' column I in all sheets'

    End With

End Sub

These patients are divided into 5 different sheets as we have 5 different vendors. So far, I manage to get the Listview to appear and successfully list ALL my subjects in the ListView as shown with the code below:

Private Sub btnActu_Click()

    Dim Item As ListItem
    Dim LastRow As Integer
    Dim i As Integer
    Dim Wks As Worksheet

    ListView1.ListItems.Clear
        
    For Each Wks In Sheets(Array(2, 3, 4, 5, 6))
    LastRow = Wks.Cells(Rows.Count, 1).End(xlUp).Row
    
        For i = 2 To LastRow
    
            Set Item = ListView1.ListItems.Add(Text:=Wks.Cells(i, 1))
    
            Item.SubItems(1) = Wks.Cells(i, 2)
            Item.SubItems(2) = Wks.Cells(i, 3)
            Item.SubItems(3) = Wks.Cells(i, 4)
            Item.SubItems(4) = Wks.Cells(i, 5)
            Item.SubItems(5) = Wks.Cells(i, 6)
            Item.SubItems(6) = Wks.Cells(i, 7)
            Item.SubItems(7) = Wks.Cells(i, 8)
            Item.SubItems(8) = Wks.Cells(i, 9)

        Next i
    
    Next Wks
    
    lblNbReg.Caption = ListView1.ListItems.Count

End Sub

Now here is the issue: I am trying to add a "SEARCH" button to search subjects, in all the 5 sheets in question, either by NAME (column C) or BIRTH DATE (column E), and display all the results in the ListView.

On top of the ListView, I have added textboxes as search criteria.

Could you please help me?

Many thanks in advance

 

[DanteAmor]

I am trying to add a "SEARCH" button to search subjects, in all the 5 sheets in question, either by NAME (column C) or BIRTH DATE (column E)

"Date of birth", Width:=60 ' column G in all sheets'

According to your code the birthday column is G.

btnActu_Click

We can use the same button to make the filter.
In textbox1 the name and in textbox2 the date.
You can leave both fields blank and you will get all the data, you can put the name or a few letters of a name, or you can filter by date or by name and date.
Try the following:

Private Sub btnActu_Click()
  Dim Item As ListItem
  Dim i As Long
  Dim Wks As Worksheet
  Dim crt1 As String, crt2 As Variant
 
  ListView1.ListItems.Clear
  For Each Wks In Sheets(Array(2, 3, 4, 5, 6))
    For i = 2 To Wks.Cells(Rows.Count, 1).End(xlUp).Row
      If TextBox1.Value = "" Then crt1 = Wks.Cells(i, "C") Else crt1 = TextBox1.Value
      If TextBox2.Value = "" Then crt2 = Wks.Cells(i, "G") Else crt2 = CDate(TextBox2.Value)
      If LCase(Wks.Cells(i, "C")) Like "*" & LCase(crt1) & "*" And Wks.Cells(i, "G") = crt2 Then
        Set Item = ListView1.ListItems.Add(Text:=Wks.Cells(i, 1))
        Item.SubItems(1) = Wks.Cells(i, 2)
        Item.SubItems(2) = Wks.Cells(i, 3)
        Item.SubItems(3) = Wks.Cells(i, 4)
        Item.SubItems(4) = Wks.Cells(i, 5)
        Item.SubItems(5) = Wks.Cells(i, 6)
        Item.SubItems(6) = Wks.Cells(i, 7)
        Item.SubItems(7) = Wks.Cells(i, 8)
        Item.SubItems(8) = Wks.Cells(i, 9)
      End If
    Next i
  Next Wks
  lblNbReg.Caption = ListView1.ListItems.Count
End Sub

 

[Rnarde]

Hello DanteAmor!

This works just like a charm, I'm amazed ! Thank you so much for this, I owe you big time.

Now I will play around with it to better myself on VBA !

Have a great day.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.