Searching Items in List View

[Mohsin110]

Hi, just want to display the items in list view which matching with the textbox text, but my codes are not working for me, could you please help me to correct my code.

Option Explicit
    Dim wksSource As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim LstItem As ListItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long

'listViw Initialize
Private Sub UserForm_Initialize()
    With Me.ListView1
        .Gridlines = True
        .View = lvwReport
    End With
          pData      
End Sub

'Loading Data to listView
 Sub pData()
   
   
    Set wksSource = ThisWorkbook.Worksheets("mrnData")
    Set rngData = wksSource.Range("A1").CurrentRegion
   
    'column headers
    For Each rngCell In rngData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
    Next rngCell
   
    'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
   
    'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
   
    'Fill the ListView
    For i = 2 To RowCount
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        For j = 2 To ColCount
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
        Next j
    Next i
   
End Sub

'Searching through listView
Private Sub TextBox1_Change() 
 Dim fString As Variant
    fString = TextBox1.Text & "*"
       
        pData
        With Me.ListView1
                For i = .ListItems.Count To 1 Step -1
                        If Not (LCase(.ListItems(i) Like LCase(fString))) Then
                            .ListItems.Remove i
                    End If
                Next i
    End With
End Sub

 

[Bosquedeguate]

Please explain what you mean by “is not working”.

Also provide a sample worksheet with data you are displaying in the Listview control.

 

[Mohsin110]

The data is loading successfully in list view from excel table but searching by search box in list view is not working, the problem is only with searching part of code i think. i have attached the excel workbook please check & help.

Sample = MyWorkbook

 

[Domenic]

I haven't look at your sample workbook, but I would suggest that you take another approach. When the Change event is executed, first clear the ListView, then loop through your data, and then load onto the ListView any row that meets the criteria.

Private Sub TextBox1_Change()
    Dim fString As Variant
    fString = TextBox1.Text & "*"
    
    With Me.ListView1
        .ListItems.Clear
        For i = 2 To RowCount
            If LCase(rngData(i, 1)) Like LCase(fString) Then
                Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
                For j = 2 To ColCount
                    LstItem.ListSubItems.Add Text:=rngData(i, j).Value
                Next j
            End If
        Next i
    End With
End Sub

Hope this helps!

 

[Mohsin110]

I haven't look at your sample workbook, but I would suggest that you take another approach. When the Change event is executed, first clear the ListView, then loop through your data, and then load onto the ListView any row that meets the criteria.

Private Sub TextBox1_Change()
    Dim fString As Variant
    fString = TextBox1.Text & "*"
   
    With Me.ListView1
        .ListItems.Clear
        For i = 2 To RowCount
            If LCase(rngData(i, 1)) Like LCase(fString) Then
                Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
                For j = 2 To ColCount
                    LstItem.ListSubItems.Add Text:=rngData(i, j).Value
                Next j
            End If
        Next i
    End With
End Sub

Hope this helps!

I am really grateful, you have solve my problem, thank you so much.
I have to add reference of all the columns in your suggested code to search through all the columns in list view. Its working perfectly now.

Private Sub TextBox1_Change()
   Dim fString As Variant
   Dim s As Long
   
    fString = TextBox1.text & "*"
    
    With Me.ListView1
        .ListItems.Clear
        For s = 2 To RowCount
            If (LCase(rngData(s, 1)) Like LCase(fString) Or LCase(rngData(s, 2)) Like LCase(fString) _
                Or LCase(rngData(s, 4)) Like LCase(fString) Or LCase(rngData(s, 5)) Like LCase(fString) _
                Or LCase(rngData(s, 6)) Like LCase(fString) Or LCase(rngData(s, 7)) Like LCase(fString) _
                Or LCase(rngData(s, 7)) Like LCase(fString) Or LCase(rngData(s, 8)) Like LCase(fString) _
                Or LCase(rngData(s, 9)) Like LCase(fString) Or LCase(rngData(s, 10)) Like LCase(fString) _
                Or LCase(rngData(s, 11)) Like LCase(fString) Or LCase(rngData(s, 16)) Like LCase(fString)) Then
            
                Set LstItem = .ListItems.Add(text:=rngData(s, 1).Value)
                For j = 2 To ColCount
                    LstItem.ListSubItems.Add text:=rngData(s, j).Value
                Next j
            End If
        Next s
    End With
End Sub

 

[Domenic]

That's great, glad you've got it working.

Cheers!

 

[Mohsin110]

Hi, I fill the values in form by double click row of list view, to edit some values and then press the Update button to save changes, but it just able to make changes only in the last row, rest of rows do not update by pressing update button, hope you understand what I'm saying. The update button VBA code is mentioned below please help me if there is any problem in the code. Is there any option to send you a video to review?

Sub UpdateBtn()

    Set sh = ThisWorkbook.Sheets("MRN")                 'Sheet 1
    Set sh2 = ThisWorkbook.Sheets("mrnData")          'Sheet 2
    Set tbl = sh2.ListObjects("mrnDbase")                   'Table
   
    LR = sh2.Cells(Rows.Count, 1).End(xlUp).Row        'Table rows
        
    For l = 2 To LR
        If sh2.Cells(LR, 1).Value = Range("ID").Value Then
            With sh2
                '.Cells(LR, 1) = ID
                '.Cells(LR, 2) = Range("ref").Value
                '.Cells(LR, 3) = Range("Date").Value
                .Cells(LR, 4) = Range("Supplier").Value
                .Cells(LR, 5) = Range("PO").Value
                .Cells(LR, 6) = Range("Mode").Value
                .Cells(LR, 7) = Range("Forwarder").Value
                .Cells(LR, 8) = Range("CustomDeclaration").Value
                .Cells(LR, 9) = Range("AWB").Value
                .Cells(LR, 10) = Range("EDAS").Value
                .Cells(LR, 11) = Range("CustomDuty").Value
                .Cells(LR, 12) = Range("DisbursFee").Value
                .Cells(LR, 13) = Range("OtherBOE").Value
                .Cells(LR, 14) = Range("VAT").Value
                .Cells(LR, 15) = Range("AdminFee").Value
                .Cells(LR, 16) = Range("Fquote").Value
                .Cells(LR, 17) = Range("ExpressFre").Value
                .Cells(LR, 18) = Range("DocAttest").Value
                .Cells(LR, 19) = Range("Handling").Value
                .Cells(LR, 20) = Range("ACombinedPO").Value
                .Cells(LR, 21) = Range("Combined").Value
                .Cells(LR, 22) = Range("Note").Value
            End With
        End If
    Next l
End Sub

 

[Bosquedeguate]

The logic in the IF statement inside your loop appears to be the problem ....
Your code is

If sh2.Cells(LR, 1).Value = Range("ID").Value Then

This only updates row LR.

Try changing your code to

        If sh2.Cells(l, 1).Value = Range("ID").Value Then

(change "LR" to "l") and see if that works better for you.

 

[Mohsin110]

The logic in the IF statement inside your loop appears to be the problem ....
Your code is

If sh2.Cells(LR, 1).Value = Range("ID").Value Then

This only updates row LR.

Try changing your code to

        If sh2.Cells(l, 1).Value = Range("ID").Value Then

(change "LR" to "l") and see if that works better for you.

Thanks a lot, you have solved my problem.

 

[Mohsin110]

Please suggest me a VBA code to delete the record from excel table with button click by matching the row ID of the selected row in listView.
I have loaded the table data in listView with the following code below.

'Loading Data to listView
 Sub mData()
    
    Set wksSource = ThisWorkbook.Worksheets("mrnData")
    Set rngData = wksSource.Range("A1").CurrentRegion
    
    'Adding column headers
    With Me.ListView1
        For Each rngCell In rngData.Rows(1).Cells
            .ColumnHeaders.Add Text:=rngCell.Value, Width:=90
        Next rngCell
        
        .ColumnHeaders(1).Width = 30
        .ColumnHeaders(2).Width = 35
        .ColumnHeaders(3).Width = 70
        .ColumnHeaders(4).Width = 50
        .ColumnHeaders(5).Width = 35
        .ColumnHeaders(6).Width = 35
        .ColumnHeaders(7).Width = 70
        .ColumnHeaders(9).Width = 109
        .ColumnHeaders(10).Width = 100
        .ColumnHeaders(11).Width = 70
        .ColumnHeaders(13).Width = 80
        .ColumnHeaders(14).Width = 35
        .ColumnHeaders(16).Width = 70
        .ColumnHeaders(17).Width = 70
        
        For i = 11 To 19
          .ColumnHeaders(i).Alignment = lvwColumnRight
        Next i
        
    End With
    
    'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
    
    'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
    
    'Fill the ListView
    For i = 2 To RowCount
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        For j = 2 To ColCount
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
        Next j
    Next i
    
End Sub