search all the column in listview with wildcard

Option Explicit

Private Sub Form_Load()
    Dim bResult As Boolean
    Dim i As Long
    Const Pattern As String = "?ab*"
   
    For i = 1 To ListView1.ListItems.Count
       bResult = ListView1.ListItems(i).Text Like Pattern
       If bResult Then
          Debug.Print "Match found at ListView1.ListItems "; i
       End If
    Next
End Sub

Hi danaseaman,

your code have below error.

Compile error:
Constant expression required.

in line

Const Pattern As String = "?ab*"


I need the code can highlight the search result in the same listview. Pls advice.

dim pattern as string
Pattern = "?ab*"




**please do not give points to this answer, since danaseaman answered your question, i just corrected an error of him**

hi Mark_FreeSoftware,

after i follow your code but still don't work.Pls advice

does it prints some text to the debug screen?

try the following code:
(it is the modified version of the code from danaseaman )

Option Explicit

Private Sub Form_Load()
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = "?ab*"
   
   Fillist
   
   
    For i = 1 To ListView1.ListItems.Count
       ListView1.ListItems(i).Selected = False
       bResult = ListView1.ListItems(i).Text Like Pattern
       If bResult Then
          Debug.Print "Match found at ListView1.ListItems "; i
          ListView1.ListItems(i).Selected = True
       End If
    Next
End Sub


Private Sub Fillist()
   ListView1.ListItems.Add , , "abcd"
   ListView1.ListItems.Add , , "1abcd"
   ListView1.ListItems.Add , , "2abcd"
   ListView1.ListItems.Add , , "3abcd"
   ListView1.ListItems.Add , , "4abcd123"
   ListView1.ListItems.Add , , "abcd4312"
   ListView1.ListItems.Add , , "abcd4"
   ListView1.ListItems.Add , , "abcd4134"
   ListView1.ListItems.Add , , "bd"
End Sub

hi Mark_FreeSoftware,

pls find the listview1 data like below

abc123
abc2345
abc256
567abc

when use key "5" in a text box the listview1 show result like below

abc2345
abc256
567abc

when use key "56" in a text box the listview1 show result like below

abc256
567abc

when use backspace once "56" in a text box and left "5" in the textbox and the listview1 show result like below

abc2345
abc256
567abc

Pls advice. thank you.

can you try to spell that different, since i can't exactly follow what you are saying (english is not my native language)

Try using Instr instead of Like:
bResult =Instr( ListView1.ListItems(i).Text, Pattern)

Hi Guys,

in the form i have  1 listview1 and one textbox1.

i need a code in Private Sub Textbox1_Change()

I have listview1 populate data like below.

abc123
abc2345
abc256
567abc

when user key in "5" in the textbox1 then listview1 will show result like below
( the code will search 5 in the listview1 data and display result into listview1 )

abc2345
abc256
567abc

when user key in "56" in the textbox1 then listview1 show result like below
( the code will search 56 in the listview1 data and display result into listview1 )

abc256
567abc

when use backspace once in the textbox1 that have "56" and left "5" in the textbox and the listview1 show result like below
( the code will search 5 in the listview1 data and display result into listview1 )

abc2345
abc256
567abc

then you could try this code:


Option Explicit

Private Sub Text1_Change()
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = Text1.Text
    For i = 1 To ListView1.ListItems.Count
       ListView1.ListItems(i).Selected = False
       bResult = CBool(InStr(ListView1.ListItems(i).Text, Pattern))
       If bResult Then
          'Debug.Print "Match found at ListView1.ListItems "; i
          ListView1.ListItems(i).Selected = True
       End If
    Next
End Sub

Private Sub Form_Load()
   ListView1.ListItems.Add , , "abcd"
   ListView1.ListItems.Add , , "1abcd"
   ListView1.ListItems.Add , , "2abcd"
   ListView1.ListItems.Add , , "3abcd"
   ListView1.ListItems.Add , , "4abcd123"
   ListView1.ListItems.Add , , "abcd4312"
   ListView1.ListItems.Add , , "abcd4"
   ListView1.ListItems.Add , , "abcd4134"
   ListView1.ListItems.Add , , "bd"
   
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
   ListView1.HideSelection = False
   ListView1.MultiSelect = True
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
End Sub

Hi Mark_FreeSoftware,

the code work but i have few thousand data in the listview1. the code only highlight the data and the user still need to check 1 by 1.
can we remove all those unwanted data and only display result in the listview1. Pls advice.

Thank you in advance.
 

oww, that's a different question again


so you want to remove items that do not match?

working on it

this is the update text1_change sub, please note the looping starts at the end!


Private Sub Text1_Change()
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = Text1.Text
    For i = ListView1.ListItems.Count To 1 Step -1
       ListView1.ListItems(i).Selected = False
       bResult = CBool(InStr(ListView1.ListItems(i).Text, Pattern))
       If Not bResult Then
          ListView1.ListItems.Remove i
       End If
    Next
End Sub

Hi Mark_FreeSoftware,

Sorry for so many question i really stuck here. Pls help.
in the textbox1 that have "56" and when use backspace once left "5" in the textbox and the listview1 show result like below
( the code will search 5 all over again in the listview1 data and display result into listview1 )

abc2345
abc256
567abc

then you have to add all the items before doing a new search

because my last post only remove items, but it doesnt make things visible again.


what you could try is this:


add 2 listviews,
one visible one not

add all the items to the invisible listview (listview1)

then instead of removing them from listview1, add them to the visible listview2

Hi Mark_FreeSoftware,

How about the code in there should i change to use listview2. maybe you can help to post the new code there. By the way how to make the listview not visible.

Thank you.

add a secondary listview, (listview2)

place it where you want to see the listview

then use this code:



Option Explicit

Private Sub Text1_Change()
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = Text1.Text
    ListView2.ListItems.Clear
    For i = 1 To ListView1.ListItems.Count
       bResult = CBool(InStr(ListView1.ListItems(i).Text, Pattern))
       If bResult Then
          ListView2.ListItems.Add , , ListView1.ListItems(i).Text
       End If
    Next
End Sub

Private Sub Form_Load()
   'this is just test data, add your data here:
   ListView1.ListItems.Add , , "abcd"
   ListView1.ListItems.Add , , "1abcd"
   ListView1.ListItems.Add , , "2abcd"
   ListView1.ListItems.Add , , "3abcd"
   ListView1.ListItems.Add , , "4abcd123"
   ListView1.ListItems.Add , , "abcd4312"
   ListView1.ListItems.Add , , "abcd4"
   ListView1.ListItems.Add , , "abcd4134"
   ListView1.ListItems.Add , , "bd"
   
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
   ListView2.HideSelection = False
   ListView2.MultiSelect = True
   ListView1.Visible = False         'make listview1 invisible
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
End Sub

Hi Mark_FreeSoftware,

sorry to disturb you again.

in the listview1 i have column2 and column3 like below. which i use to link to other Database.

Below is the column name
Desc                BrandID            NameID
 abcd                     1                     2
 1abcd                   1                     6
 2abcd                   1                     3
 3abcd                   3                     5
 4abcd123               3                    9
 abcd4312               6                   12
 abcd4                    8                   32
 abcd4134               8                    2


I need the search result to copy the BrandID and nameID over to listview2. like below
Desc                BrandID            NameID
 abcd4                    8                   32
 abcd4134               8                    2

Thank You,

can you show me the code how you fill the listview?

Dear mark,

Pls find below fill listview code.


Public Sub LoadBrand()
Dim strQuery1 As String
Dim itX As ListItem
Call initDB

Set rs1 = New ADODB.Recordset
On Error GoTo errorTrap
'Load Brand Into Listview6
strQuery1 = "Select Brand,BrandID From brandt"

ListView6.ListItems.Clear

rs1.Open strQuery1, cn, adOpenDynamic, adLockOptimistic
If Not rs1.EOF Then
    rs1.MoveFirst
 
    Do Until rs1.EOF
       
    If Not IsNull(rs1.Fields("Brand")) Then
    Set itX = ListView6.ListItems.Add(, , Trim(rs1.Fields("Brand")))
    Else
    Set itX = ListView6.ListItems.Add(, , (""))

    End If
    If Not IsNull(rs1.Fields("BrandID")) Then
    itX.SubItems(1) = Trim(rs1.Fields("BrandID"))
    Else
    itX.SubItems(1) = ("")
    End If
 
        rs1.MoveNext
    Loop

End If

rs1.Close
Set rs1 = Nothing
cn.Close
Set cn = Nothing

Exit Sub
errorTrap:
MsgBox Err.Description & vbCrLf & Err.HelpContext & vbCrLf & Err.HelpFile & vbCrLf & Err.LastDllError & vbCrLf & Err.Number & vbCrLf & Err.Source

End Sub

Hi Mark,

Any clue yet. Pls help

thank You,

Option Explicit

Private Sub Text1_Change()
Dim n As Long
    Dim bResult As Boolean
    Dim i As Long
    Dim Pattern As String
    Pattern = Text1.Text
    ListView2.ListItems.Clear
    For i = 1 To ListView1.ListItems.Count
       bResult = CBool(InStr(ListView1.ListItems(i).Text, Pattern))
       If bResult Then
         ListView2.ListItems.Add , , ListView1.ListItems(i).Text
         For n = 1 To 2
            ListView2.ListItems(i).SubItems(n) = ListView1.ListItems(i).SubItems(n)
         Next
       End If
    Next
End Sub

Private Sub Form_Load()
Dim itX As ListItem, a As Long
   
   For a = 1 To 3
      ListView1.ColumnHeaders.Add
      ListView2.ColumnHeaders.Add
    Next
   
   
   Set itX = ListView1.ListItems.Add(, , "abcd")
   ListView1.ListItems(1).SubItems(1) = "aa"
   ListView1.ListItems(1).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "1abcd")
   ListView1.ListItems(2).SubItems(1) = "aa"
   ListView1.ListItems(2).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "2abcd")
   ListView1.ListItems(3).SubItems(1) = "aa"
   ListView1.ListItems(3).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "3abcd")
   ListView1.ListItems(4).SubItems(1) = "aa"
   ListView1.ListItems(4).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "4abcd123")
   ListView1.ListItems(5).SubItems(1) = "aa"
   ListView1.ListItems(5).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "abcd4312")
   ListView1.ListItems(6).SubItems(1) = "aa"
   ListView1.ListItems(6).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "abcd4")
   ListView1.ListItems(7).SubItems(1) = "aa"
   ListView1.ListItems(7).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "abcd4134")
   ListView1.ListItems(8).SubItems(1) = "aa"
   ListView1.ListItems(8).SubItems(2) = "bb"
   Set itX = ListView1.ListItems.Add(, , "bd")
   ListView1.ListItems(9).SubItems(1) = "aa"
   ListView1.ListItems(9).SubItems(2) = "bb"
   
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
   ListView2.HideSelection = False
   ListView2.MultiSelect = True
   ListView1.Visible = False
   ListView2.View = lvwReport
   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!important!!!!!!!!!!!!!!!!!!!!!!!
End Sub

Dear Mark,

When there is 1 result let it have below error.


run-time error '35600'

Index out of bounds

Error in below line code.

           ListView2.ListItems(i).SubItems(n) = ListView1.ListItems(i).SubItems(n)

Pls advice.