Search Function in Userform With List Box

M

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Screenshot 2021-10-10 174549.png
    Screenshot 2021-10-10 174549.png
    66.3 KB · Views: 92
Sort by date Sort by votes
hmmm...
Could you tell me in which part of your initial question it says that you want the search without case sensitivity, in which part it says that you want to select the first record that matches, press again and that select the second that matches and so on.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
DanteAmor said:
hmmm...
Could you tell me in which part of your initial question it says that you want the search without case sensitivity, in which part it says that you want to select the first record that matches, press again and that select the second that matches and so on.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
Click to expand...
Hmm think ill just settle for what youve help me for now.thank you so much.
 
Upvote 0
mrsec said:
more like a partial search
Click to expand...
Try the following 2 options, to see which one is closest to your need:

VBA Code: 
Private Sub SearchButton_Click()
  Dim i As Long, j As Long, n As Long
  Dim sc As Variant
  
  j = Len(SearchBox)
  With AvailableNumberList
    If .ListIndex = -1 Or .ListIndex = .ListCount - 1 Then n = 0 Else n = .ListIndex + 1
    For i = n 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))
          .ListIndex = i
          Exit For
      End Select
    Next i
  End With
End Sub

VBA Code: 
Private Sub SearchButton_Click()
  Dim i As Long, j As Long, n As Long
  Dim sc As Variant
  
  j = Len(SearchBox)
  With AvailableNumberList
    If .ListIndex = -1 Or .ListIndex = .ListCount - 1 Then n = 0 Else n = .ListIndex + 1
    For i = n To .ListCount - 1
      If LCase(.List(i, 0)) Like "*" & LCase(SearchBox.Text) & "*" Or _
         LCase(.List(i, 1)) Like "*" & LCase(SearchBox.Text) & "*" Or _
         LCase(.List(i, 2)) Like "*" & LCase(SearchBox.Text) & "*" Or _
         LCase(.List(i, 3)) Like "*" & LCase(SearchBox.Text) & "*" Or _
         LCase(.List(i, 4)) Like "*" & LCase(SearchBox.Text) & "*" Or _
         LCase(.List(i, 5)) Like "*" & LCase(SearchBox.Text) & "*" Or _
         LCase(.List(i, 6)) Like "*" & LCase(SearchBox.Text) & "*" Then
          .ListIndex = i
          Exit For
      End If
    Next i
  End With
End Sub
 
Upvote 0
Here a more complete option to search for partial text, when it reaches the end, it starts again.

VBA Code: 
Private Sub SearchButton_Click()
  Dim i As Long, j As Long, n As Long
  Dim sc As Variant, bln As Boolean
  
  j = Len(SearchBox)
  With AvailableNumberList
    If .ListIndex = -1 Or .ListIndex = .ListCount - 1 Then n = 0 Else n = .ListIndex + 1
    Do While True
      For i = n To .ListCount - 1
        If LCase(.List(i, 0)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 1)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 2)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 3)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 4)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 5)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 6)) Like "*" & LCase(SearchBox.Text) & "*" Then
            .ListIndex = i
            bln = True
            Exit Sub
        End If
      Next i
      If bln Then
        Exit Do
      Else
        If n = 0 Then
          MsgBox "No match"
          .ListIndex = -1
          SearchBox.SetFocus
          Exit Do
        Else
          n = 0
        End If
      End If
    Loop
  End With
End Sub
 
Upvote 0
DanteAmor said:
Here a more complete option to search for partial text, when it reaches the end, it starts again.

VBA Code: 
Private Sub SearchButton_Click()
  Dim i As Long, j As Long, n As Long
  Dim sc As Variant, bln As Boolean
 
  j = Len(SearchBox)
  With AvailableNumberList
    If .ListIndex = -1 Or .ListIndex = .ListCount - 1 Then n = 0 Else n = .ListIndex + 1
    Do While True
      For i = n To .ListCount - 1
        If LCase(.List(i, 0)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 1)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 2)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 3)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 4)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 5)) Like "*" & LCase(SearchBox.Text) & "*" Or _
           LCase(.List(i, 6)) Like "*" & LCase(SearchBox.Text) & "*" Then
            .ListIndex = i
            bln = True
            Exit Sub
        End If
      Next i
      If bln Then
        Exit Do
      Else
        If n = 0 Then
          MsgBox "No match"
          .ListIndex = -1
          SearchBox.SetFocus
          Exit Do
        Else
          n = 0
        End If
      End If
    Loop
  End With
End Sub
Click to expand...
Awesome it is working haha finally the "loop" thing i mentioned.it hits the spot.
Thank you so so much for the effort and apology if I confused you.appreciate the help so much.bless you man.thanks!!
 
Upvote 0
Macro with enhancement

VBA Code: 
Private Sub SearchButton_Click()
  Dim i As Long, n As Long
  
  With AvailableNumberList
    If .ListIndex = -1 Or .ListIndex = .ListCount - 1 Then n = 0 Else n = .ListIndex + 1
    Do While True
      For i = n To .ListCount - 1
        If LCase(.List(i, 0) & .List(i, 1) & .List(i, 2) & .List(i, 3) & .List(i, 4) & _
          .List(i, 5) & .List(i, 6)) Like "*" & LCase(SearchBox.Text) & "*" Then
            .ListIndex = i
            Exit Sub
        End If
      Next i
      If n = 0 Then
        MsgBox "No match"
        .ListIndex = -1
        SearchBox.SetFocus
        Exit Do
      Else
        n = 0
      End If
    Loop
  End With
End Sub
 
Upvote 0
Solution
DanteAmor said:
Macro with enhancement

VBA Code: 
Private Sub SearchButton_Click()
  Dim i As Long, n As Long
 
  With AvailableNumberList
    If .ListIndex = -1 Or .ListIndex = .ListCount - 1 Then n = 0 Else n = .ListIndex + 1
    Do While True
      For i = n To .ListCount - 1
        If LCase(.List(i, 0) & .List(i, 1) & .List(i, 2) & .List(i, 3) & .List(i, 4) & _
          .List(i, 5) & .List(i, 6)) Like "*" & LCase(SearchBox.Text) & "*" Then
            .ListIndex = i
            Exit Sub
        End If
      Next i
      If n = 0 Then
        MsgBox "No match"
        .ListIndex = -1
        SearchBox.SetFocus
        Exit Do
      Else
        n = 0
      End If
    Loop
  End With
End Sub
Click to expand...
Awesome stuff.thank you really so much for all the the effort of helping me.cabt thank you enough.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Display textbox from listbox - getting 381 error
Replies
4
Views
192
maiatanaka
M
M
  • Solved
Search function for listbox userform
Replies
9
Views
508
Megg
M
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
404
pure vito
P
I
  • Question
Listbox not being populate when code is run
Replies
4
Views
250
ipbr21054
I
P
  • Solved
Userform textbox format when populated by listbox
Replies
9
Views
350
dmt32
D

Forum statistics

Threads
1,214,375
Messages
6,119,164
Members
448,870
Latest member
max_pedreira

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