Search Function in Userform With List Box

[mrsec]

Good Day,
I am trying to write a code for a search button which searches a listbox based a specific input set in a textbox. The values searched are number or words, and the listbox contains values from a single column. The code i found below(ctto) but i don't understand why it only works for the first column of my list box.

• SearchButton: A Button which upon clicking is supposed to initiate the search
• SearchBox: The textbox which will contain the search value
• AvailableNumberList: The listbox which contains the data
• About 20 Columns in Total but I just want to search from Column A to G

Private Sub SearchButton_Click()

Dim SearchCriteria, i, n As Double
SearchCriteria = Me.SearchBox.Value
n = AvailableNumberList.ListCount

For i = 0 To n - 1
If Left(AvailableNumberList.List(i),Len(SearchCriteria))=SearchCriteria Then
AvailableNumberList.ListIndex = i
Exit For
End If
Next i

End Sub

 

[DanteAmor]

You must compare with the 7 columns

Try this:

Private Sub SearchButton_Click()
  Dim i As Long, j As Long
  Dim sc As Variant
  
  j = Len(SearchBox)
  With AvailableNumberList
    For i = 0 To .ListCount - 1
      Select Case SearchBox.Text
        Case Left(.List(i, 0), j), Left(.List(i, 1), j), Left(.List(i, 2), j), _
          Left(.List(i, 3), j), Left(.List(i, 4), j), Left(.List(i, 5), j), Left(.List(i, 6), j)
          .ListIndex = i
          Exit For
      End Select
    Next i
  End With
End Sub

 

[mrsec]

You must compare with the 7 columns

Try this:

Private Sub SearchButton_Click()
  Dim i As Long, j As Long
  Dim sc As Variant
 
  j = Len(SearchBox)
  With AvailableNumberList
    For i = 0 To .ListCount - 1
      Select Case SearchBox.Text
        Case Left(.List(i, 0), j), Left(.List(i, 1), j), Left(.List(i, 2), j), _
          Left(.List(i, 3), j), Left(.List(i, 4), j), Left(.List(i, 5), j), Left(.List(i, 6), j)
          .ListIndex = i
          Exit For
      End Select
    Next i
  End With
End Sub

code works,but how do i make it case insensitive?
and make it "loop"when i have similar word in different rows?(image attached)

Thank You.

 

[DanteAmor]

and make it "loop"when i have similar word in different rows?(image attached)

I don't understand, but I guess if multiple records meet the condition then the code should select them.

Try:

Private Sub SearchButton_Click()
  Dim i As Long, j As Long
  Dim sc As Variant
  
  j = Len(SearchBox)
  With AvailableNumberList
    .MultiSelect = fmMultiSelectMulti
    For i = 0 To .ListCount - 1
      Select Case LCase(SearchBox.Text)
        Case LCase(Left(.List(i, 0), j)), LCase(Left(.List(i, 1), j)), LCase(Left(.List(i, 2), j)), _
             LCase(Left(.List(i, 3), j)), LCase(Left(.List(i, 4), j)), LCase(Left(.List(i, 5), j)), _
             LCase(Left(.List(i, 6), j))
          .Selected(i) = True
      End Select
    Next i
  End With
End Sub

 

[mrsec]

I don't understand, but I guess if multiple records meet the condition then the code should select them.

Try:

Private Sub SearchButton_Click()
  Dim i As Long, j As Long
  Dim sc As Variant
 
  j = Len(SearchBox)
  With AvailableNumberList
    .MultiSelect = fmMultiSelectMulti
    For i = 0 To .ListCount - 1
      Select Case LCase(SearchBox.Text)
        Case LCase(Left(.List(i, 0), j)), LCase(Left(.List(i, 1), j)), LCase(Left(.List(i, 2), j)), _
             LCase(Left(.List(i, 3), j)), LCase(Left(.List(i, 4), j)), LCase(Left(.List(i, 5), j)), _
             LCase(Left(.List(i, 6), j))
          .Selected(i) = True
      End Select
    Next i
  End With
End Sub

Case sensitive works.
For the "loop"i mentioned i attached a picture for you to view.

 

[mrsec]

Case sensitive works.
For the "loop"i mentioned i attached a picture for you to view.

If i have the same exact word it will only search the last entry.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Search Function userform
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mrsec]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Search Function userform
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Noted.thank you.

 

[DanteAmor]

If i have the same exact word it will only search the last entry.

I assume you mean select the last record found.

Try:

Private Sub SearchButton_Click()
  Dim i As Long, j As Long
  Dim sc As Variant
  
  j = Len(SearchBox)
  With AvailableNumberList
    .MultiSelect = fmMultiSelectMulti
    For i = .ListCount - 1 to 0 step -1
      Select Case LCase(SearchBox.Text)
        Case LCase(Left(.List(i, 0), j)), LCase(Left(.List(i, 1), j)), LCase(Left(.List(i, 2), j)), _
             LCase(Left(.List(i, 3), j)), LCase(Left(.List(i, 4), j)), LCase(Left(.List(i, 5), j)), _
             LCase(Left(.List(i, 6), j))
           .ListIndex = i
           exit for
      End Select
    Next i
  End With
End Sub

 

[mrsec]

I assume you mean select the last record found.

Try:

Private Sub SearchButton_Click()
  Dim i As Long, j As Long
  Dim sc As Variant
 
  j = Len(SearchBox)
  With AvailableNumberList
    .MultiSelect = fmMultiSelectMulti
    For i = .ListCount - 1 to 0 step -1
      Select Case LCase(SearchBox.Text)
        Case LCase(Left(.List(i, 0), j)), LCase(Left(.List(i, 1), j)), LCase(Left(.List(i, 2), j)), _
             LCase(Left(.List(i, 3), j)), LCase(Left(.List(i, 4), j)), LCase(Left(.List(i, 5), j)), _
             LCase(Left(.List(i, 6), j))
           .ListIndex = i
           exit for
      End Select
    Next i
  End With
End Sub

hmm..more like a partial search,like when I have the same content in different rows after I press the button it will go to the first one and if I click on the search button again it will select the 2nd row that has similar value.