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
(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