VBA: search in multiple sheets & display in ListView

Rnarde

New Member
Joined
Jul 15, 2020
Messages
2
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:

VBA Code:
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:

VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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:

VBA Code:
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

New Member
Joined
Jul 15, 2020
Messages
2
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,518
Messages
5,548,519
Members
410,843
Latest member
NZJohn
Top