Searching Items in List View

M

Mohsin110

New Member
Joined
Aug 26, 2023
Messages
23
Office Version
  1. 2021
Platform
  1. Windows
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.

VBA 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
 
Last edited by a moderator:
Sort by date Sort by votes
Do you have a column in your list to store the row number? If you do then when you click on the delete button you can use the value to delete the
desired row in your worksheet.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Here's an example that I hope you can use. It should be self explanatory, but don't hesitate to ask if you have any questions.

VBA Code: 
Option Explicit

Private Sub CommandButton1_Click()
   
    Dim id As String
    Dim i As Long
    id = ""
    With Me.ListView1
        For i = 1 To .ListItems.Count
            If .ListItems(i).Selected = True Then
                id = .ListItems(i).Text
                Exit For
            End If
        Next i
    End With
   
    If Len(id) = 0 Then
        MsgBox "Please select a list item, and try again!", vbExclamation
        Exit Sub
    End If
   
    Dim wksSource As Worksheet
    Set wksSource = ThisWorkbook.Worksheets("mrnData")
   
    Dim rngData As Range
    Set rngData = wksSource.Range("A1").CurrentRegion
   
    Dim rngFoundID As Range
    Set rngFoundID = rngData.Columns(1).Find(what:=id, LookIn:=xlValues, lookat:=xlWhole)
       
    If Not rngFoundID Is Nothing Then
        rngFoundID.EntireRow.Delete
        UpdateListView
        With Me.ListView1
            If .ListItems.Count > 0 Then
                .ListItems(1).Selected = False
            End If
        End With
    Else
        MsgBox "ID " & id & " not found!", vbExclamation
    End If
   
End Sub

Private Sub UserForm_Initialize()

    Dim wksSource As Worksheet
    Set wksSource = ThisWorkbook.Worksheets("mrnData")
   
    Dim rngData As Range
    Set rngData = wksSource.Range("A1").CurrentRegion
   
    With Me.ListView1
   
        .FullRowSelect = True
        .MultiSelect = False
        .View = lvwReport
   
        Dim rngCell As Range
        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
       
        Dim i As Long
        For i = 11 To 19
          .ColumnHeaders(i).Alignment = lvwColumnRight
        Next i
       
    End With
   
    UpdateListView
   
    With Me.ListView1
        If .ListItems.Count > 0 Then
            .ListItems(1).Selected = False
        End If
    End With

End Sub

Private Sub UpdateListView()

    Me.ListView1.ListItems.Clear

    Dim wksSource As Worksheet
    Set wksSource = ThisWorkbook.Worksheets("mrnData")
   
    Dim rngData As Range
    Set rngData = wksSource.Range("A1").CurrentRegion
   
    'Count the number of rows in the source range
    Dim RowCount As Long
    RowCount = rngData.Rows.Count
   
    'Count the number of columns in the source range
    Dim ColCount As Long
    ColCount = rngData.Columns.Count
   
    'Fill the ListView
    Dim i As Long
    Dim j As Long
    Dim LstItem As ListItem
    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

Hope this helps!
 
Upvote 0
Hi, i am trying to make alternate color of rows of ListView, but it's giving me an error "Argument not optional", please help me to solve it. the picture is attached of error.

VBA Code: 
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
        
       'Alternate row color
        If i Mod 2 = 0 Then
            LstItem.ListSubItems.Item.ForeColor = RGB(173, 189, 190)
        Else
            LstItem.ListSubItems.Item.ForeColor = RGB(18, 189, 180)
        End If
    Next i
    
End Sub
 

Attachments

  • error.png
    error.png
    53 KB · Views: 13
Upvote 0
The Item property of the ListSubItems object requires that you specify an index number that points to a subitem. For example, if you want to change the forecolor for the first subitem for a listitem...

VBA Code: 
LstItem.ListSubItems.Item(1).ForeColor = RGB(173, 189, 190)

And you'll need to individually set the forecolor for each subitem for the listitem. And you'll need to set the forecolor for the listitem itself, if what you want is to set the forecolor for the entire row. So, for example, if you want to set the forecolor for the entire row...

VBA Code: 
    Dim i As Long
    Dim j As Long
    Dim RowColor As Long
    Dim LstItem As ListItem
    Dim LstSubItem As ListSubItem
    
    'Fill the ListView
    For i = 2 To RowCount
    
       'Alternate row color
        If i Mod 2 = 0 Then
            RowColor = RGB(173, 189, 190)
        Else
            RowColor = RGB(18, 189, 180)
        End If
        
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        
        LstItem.ForeColor = RowColor
        
        For j = 2 To ColCount
            Set LstSubItem = LstItem.ListSubItems.Add(Text:=rngData(i, j).Value)
            LstSubItem.ForeColor = RowColor
        Next j
        
    Next i

Hope this helps!
 
Upvote 1
Solution
Domenic said:
The Item property of the ListSubItems object requires that you specify an index number that points to a subitem. For example, if you want to change the forecolor for the first subitem for a listitem...

VBA Code: 
LstItem.ListSubItems.Item(1).ForeColor = RGB(173, 189, 190)

And you'll need to individually set the forecolor for each subitem for the listitem. And you'll need to set the forecolor for the listitem itself, if what you want is to set the forecolor for the entire row. So, for example, if you want to set the forecolor for the entire row...

VBA Code: 
    Dim i As Long
    Dim j As Long
    Dim RowColor As Long
    Dim LstItem As ListItem
    Dim LstSubItem As ListSubItem
   
    'Fill the ListView
    For i = 2 To RowCount
   
       'Alternate row color
        If i Mod 2 = 0 Then
            RowColor = RGB(173, 189, 190)
        Else
            RowColor = RGB(18, 189, 180)
        End If
       
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
       
        LstItem.ForeColor = RowColor
       
        For j = 2 To ColCount
            Set LstSubItem = LstItem.ListSubItems.Add(Text:=rngData(i, j).Value)
            LstSubItem.ForeColor = RowColor
        Next j
       
    Next i

Hope this helps!
Click to expand...
Thank you so much for your suggestion, but it's not working for me. mentioned below is the code modified as your suggestion. please let me know if i'm doing some mistake.
It's giving me error "Object variable or With variable not set". the picture is attached for reference.

VBA Code: 
Sub mData()
    
    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
    Dim RowColor As Long
    Dim LstSubItem As ListSubItem
    
    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
    
    'Alternate row color
        If i Mod 2 = 0 Then
            RowColor = RGB(173, 189, 190)
        Else
            RowColor = RGB(18, 189, 180)
        End If
        
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        LstItem.ForeColor = RowColor
        For j = 2 To ColCount
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
            LstSubItem.ForeColor = RowColor
        Next j
    Next i
        
End Sub
 

Attachments

  • error.png
    error.png
    59.7 KB · Views: 8
Upvote 0
As per my previous post, it should be...

VBA Code: 
        For j = 2 To ColCount
            Set LstSubItem = LstItem.ListSubItems.Add(Text:=rngData(i, j).Value) 'assign the list subitem to the variable LstSubItem
            LstSubItem.ForeColor = RowColor
        Next j

Notice that we're assigning the list subitem to the variable LstSubItem.

Hope this helps!
 
Upvote 0
Domenic said:
As per my previous post, it should be...

VBA Code: 
        For j = 2 To ColCount
            Set LstSubItem = LstItem.ListSubItems.Add(Text:=rngData(i, j).Value) 'assign the list subitem to the variable LstSubItem
            LstSubItem.ForeColor = RowColor
        Next j

Notice that we're assigning the list subitem to the variable LstSubItem.

Hope this helps!
Click to expand...
Oh Yes, i just missed it, thank you so much, the code is working now.
Could you please advise, if it's possible to alternate color just background of rows of listview, instead of forecolor of text, Appreciated.
 
Upvote 0
As you probably have already noticed, the ListView object does not contain a BackColor property. So, unfortunately, it looks like it's not possible.

Cheers!
 
Upvote 1
You must log in or register to reply here.

Similar threads

R
  • Question Question
ListView Column Width
Replies
8
Views
3K
Jaafar Tribak
Jaafar Tribak
J
  • Solved
Can't get listview to update after second form inputs data
Replies
1
Views
1K
j_tyd
J
T
  • Solved
Search for Cells of certain color and empty
Replies
3
Views
346
TheTiredEngineer
T
P
  • Solved
Listview check box value sorting in textbox in ascending
Replies
3
Views
548
Domenic
D
A
  • Question Question
VBA for setting up the printing pages per sheet
Replies
0
Views
491
Aurum1
A

Forum statistics

Threads
1,223,882
Messages
6,175,166
Members
452,615
Latest member
bogeys2birdies

Share this page

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