Search column & place names in list box

[ipbr21054]

I’ve been looking for a code this evening without any luck.
On my worksheet called SHEET1 in column A is a list of customers names.
I need to be able to search for a certain customer where I have limited information on them & be able to select them from a list box.
It would work something like this.

I would have a list box which would have a search field & below it a space for the returned names.

So I type some partial info that I know in the search field & press the search button.
In this case let’s say SMI
The code would look down column A and return any name that has SMI in it & place it in the list box.
Lee SMIles
Abdul aSMIn
SMIley miley

So I now have say 3 possible customers shown in my list box.
Clicking on the name would then take me to the row that customer is in.
In this case Abdul aSMIn & the cell A258 is selected.

 

[DanteAmor]

Are the Texbox, Listbox and Button controls, are ActiveX controls or are they in a UserForm?

If it is in a user form, use this:

Open the userform, start typing in the textbox and automatically the listbox will start to fill.
Then select a name in the listbox and the cell will be selected.

Private Sub ListBox1_Click()
  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
End Sub


Private Sub TextBox1_Change()
  Dim r As Range, f As Range, cell As String
  Dim sh As Worksheet
  
  Set sh = Sheets("SHEET1")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 2
    .ColumnWidths = "100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        .AddItem f.Value
        .List(.ListCount - 1, 1) = f.Row
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
    End If
  End With
End Sub

 

[ipbr21054]

Hi,
Can you check / advise as my list box doesnt populate when i type.

UserForm1
Which consists of
TextBox1 & ListBox1

The worksheet is called Sheet1
The customers names are in column A

I have right clicked on the SHEET1 tab then pasted the code.

Here is my code,

Private Sub ListBox1_Click()
  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
End Sub
Private Sub TextBox1_Change()
  Dim r As Range, f As Range, cell As String
  Dim sh As Worksheet
 
  Set sh = Sheets("SHEET1")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 2
    .ColumnWidths = "100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("A", Range("A" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        .AddItem f.Value
        .List(.ListCount - 1, 1) = f.Row
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
    End If
  End With
End Sub

 

[ipbr21054]

@DanteAmor

Here is an example sheet for you to download and check.

http://www.mediafire.com/file/29dg9y7h6czcic9/SEARCH.xlsm/file

 

[DanteAmor]

The code goes in the userform

try this file

https://www.dropbox.com/s/u2aqpiw060kn8c5/SEARCH.xlsm?dl=0

 

[ipbr21054]

Thanks,
Ive now done it correctly & works nice.

Can i ask a couple of questions please that relate to the workings of it.

In the TextBox1_Change code ive added the following.

TextBox1 = UCase(TextBox1)

When i search for a name the letters typed that match are capitals but any that dont are lower case.

Example
If i typed your name i would see this DANTEAMOR but if i had added an extra rr at the end i would then see this DANTEAMORrr

Is this correct or was the code i used the wrong type ?

My worksheet is sorted by DATE so the column with the names in are all out of order,this is fine on my worksheet,
But when i search on the userform it takes onboard the order of the worksheet, So i then see the contents of the ListBox results out of order.
Before the names are shown in the ListBox can they be sorted A-Z then shown in order.

I have a similar code which does this but its for a ComboBox on the sheet & not a userform.
I will find it for you


Thanks very much & have a nice day.

 

[DanteAmor]

Thanks,
Ive now done it correctly & works nice.

Can i ask a couple of questions please that relate to the workings of it.

In the TextBox1_Change code ive added the following.

TextBox1 = UCase(TextBox1)

The code is not case sensitive.
You can type uppercase and you will find the names in upper and lower case.

When i search for a name the letters typed that match are capitals but any that dont are lower case.

Example
If i typed your name i would see this DANTEAMOR but if i had added an extra rr at the end i would then see this DANTEAMORrr

Is this correct or was the code i used the wrong type ?

My worksheet is sorted by DATE so the column with the names in are all out of order,this is fine on my worksheet,
But when i search on the userform it takes onboard the order of the worksheet, So i then see the contents of the ListBox results out of order.
Before the names are shown in the ListBox can they be sorted A-Z then shown in order.

I have a similar code which does this but its for a ComboBox on the sheet & not a userform.
I will find it for you


Thanks very much & have a nice day.

Try this. sorted A-Z

Private Sub ListBox1_Click()
  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select
End Sub
Private Sub TextBox1_Change()
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("SHEET1")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 2
    .ColumnWidths = "100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
              .List(i, 1) = f.Row
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value  'add at end
          .List(.ListCount - 1, 1) = f.Row
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
    End If
  End With
End Sub

 

[ipbr21054]

Many thanks,thats sorted A-Z great.

Last thing.
Im trying to close the userform after i made a selection in the listbox.
Currently i type my known info in the textbox & see the results appear in the listbox.
I look through the results in the listbox & find the customer i require & then click there name.
I then see the customer selected in my worksheet & the userform is still open.
It is at this point after i select the customer from the listbox the userform should close & leave me with the customer on my worksheet.

This is the code being used to close the userform but wherever i put it as soon as i start to type in the textbox the userform closes without even showing the results in the listbox.

Unload PostageNameSearch

Many Thanks

 

[DanteAmor]

Many thanks,thats sorted A-Z great.

Last thing.
Im trying to close the userform after i made a selection in the listbox.
Currently i type my known info in the textbox & see the results appear in the listbox.
I look through the results in the listbox & find the customer i require & then click there name.
I then see the customer selected in my worksheet & the userform is still open.
It is at this point after i select the customer from the listbox the userform should close & leave me with the customer on my worksheet.

This is the code being used to close the userform but wherever i put it as soon as i start to type in the textbox the userform closes without even showing the results in the listbox.

Unload PostageNameSearch

Many Thanks

Try

Private Sub ListBox1_Click()
  Range("A" & ListBox1.List(ListBox1.ListIndex, 1)).Select


Unload PostageNameSearch

End Sub

 

[ipbr21054]

I didnt even try that piece of code.
works great,many thanks friend