Search Listbox for first name and last name

sixman

New Member
Joined
Jan 11, 2004
Messages
11
What I have is a Userform with a listbox and multiple textboxes and buttons. The listbox shows all of the clients and info. that is stored on Sheet1 called "Database". In column A I have the clients last name stored, in Column B I have the coordinating first name stored
(i.e. A = "Doe", and B = "John"). I have a Find button which finds a client when you do a Last name search and will index to the name in the listbox. The problem I am having is when I search for clients with the same last name. What is happening is the Index will stop at the 1st name it sees in the listbox and won't go any further. I need to have a refined search where I can type in the First and Last name to do a search on.

Dim StrComp As String
ClientName = InputBox("Enter Clients Name:", "Search for Client:")
If ClientName = "" Then Exit Sub
With ListBox1
For i = 0 To .ListCount
On Error GoTo Msg
If StrComp(.List(i), ClientName, vbTextCompare) = 0 Then
.ListIndex = i
Exit For
End If
Next i
End With
MsgBox "Client Found!"
Exit Sub
Msg:
MsgBox "Client Not Found!"
Exit Sub

This same thing is bugging my EDIT code for editting my entries in the listbox. If I try to Edit someone with the same last name the indexing stops at the 1st name it sees.

Dim nameAddress As String
nameAddress = Sheets("Database").Cells.Find(What:=ListBox1.Value, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Offset(0, 0).Address
With Sheets("Database").Range(nameAddress)
tb1.Value = .Offset(0, 0)
tb2.Value = .Offset(0, 1)
tb3.Value = .Offset(0, 2)
tb4.Value = .Offset(0, 3)
tb5.Value = .Offset(0, 4)
tb6.Value = .Offset(0, 5)

I can probably remedy the situation by consolidating the First and Last names into one column but I am hoping to avoid that because I already have everything set up the way it is
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Watch MrExcel Video

Forum statistics

Threads
1,123,102
Messages
5,599,733
Members
414,332
Latest member
Hussain Almadani

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
Top