Finding Customers with the same name

[Dan Swartz]

I have a userform for pulling a customer's information. it works great. But if I have two or more customers with the same name. It only finds the first name. I live in a community of Amish. It's not uncommon to have 5 - 10 customers with the same name.

I don't even know where to start.

Is there a way to search for a name, display all customers with the same name, but show their address so i would know which customer is the correct one and then be able to choose that customer?

This is my code for the current Customer Form.

Private Sub FindCust_Click()
    
  Dim f As Range
  
  If CustomerName.Value = "" Then
    MsgBox "Please enter a customer name"
    CustomerName.SetFocus
    Exit Sub
  End If
  
  Set f = Sheet2.Range("A:A").Find(CustomerName.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    Address1.Text = Sheet2.Cells(f.Row, 2).Value
    Address2.Text = Sheet2.Cells(f.Row, 3).Value
    City.Text = Sheet2.Cells(f.Row, 4).Value
    State.Text = Sheet2.Cells(f.Row, 5).Value
    Zip.Text = Sheet2.Cells(f.Row, 6).Value
    Email.Text = Sheet2.Cells(f.Row, 7).Value
  Else
    MsgBox ("Customer does not exist"), vbOKOnly
    CustomerName.SetFocus
  End If
End Sub

 

[Dan Swartz]

Thank you in advance @Akuini !

 

[Akuini]

I will be on vacation from tomorrow till next Tuesday. I won't have much signal. I'll try and pop on to see what you got. CustomerID was C00001

Ok, have a good time then.

 

[Dan Swartz]

good afternoon @Akuini! Checking in on progress.

 

[Akuini]

good afternoon @Akuini! Checking in on progress.

Hi, did you return from your holiday?
Alright, here's what I've got:
Click on "Edit Consumer Info" to display the userform.
In the combobox, you can enter multiple keywords separated by spaces. For example, type "a pa".
If there are duplicate names, all the names will be displayed in the listbox below. Select an item in the listbox, see what happen.
I won't go into more detail for now because I want to know whether the setup is intuitively understandable.So, feel free to explore and let me know if we're heading in the right direction.

Question:
1. Approximately how many rows of data are there?
2. The code is somewhat complex because I intend for it to be both efficient and easy to use. However, the more complex the code becomes, the harder it is to comprehend and update. Therefore, this is something you need to consider. What I mean is, I could write a simpler code, but the userform might end up being less efficient to use.
Dan Swartz - Customer Database 1.xlsm

 

[Dan Swartz]

Hi, did you return from your holiday?
Alright, here's what I've got:
Click on "Edit Consumer Info" to display the userform.
In the combobox, you can enter multiple keywords separated by spaces. For example, type "a pa".
If there are duplicate names, all the names will be displayed in the listbox below. Select an item in the listbox, see what happen.
I won't go into more detail for now because I want to know whether the setup is intuitively understandable.So, feel free to explore and let me know if we're heading in the right direction.

View attachment 96974
Question:
1. Approximately how many rows of data are there?
2. The code is somewhat complex because I intend for it to be both efficient and easy to use. However, the more complex the code becomes, the harder it is to comprehend and update. Therefore, this is something you need to consider. What I mean is, I could write a simpler code, but the userform might end up being less efficient to use.
Dan Swartz - Customer Database 1.xlsm

I did get back. It was good.

I will take a look.
Question 1 answer: I don't expect more than 30,000 rows. Even then I believe it will be high. if it gets that big, i believe a bigger and better program will be purchased.
Question 2 Answer: I understand the complexity part. due to the users using this code, It needs to be Easy to use meaning more complex code. I understand I may not even understand what you wrote. but i'm taking VBA lessons and soon hopefully will understand better.

Let me look at it and play with it and I'll see if I what questions I come up with. thank you so much for your help!!!!!

 

[Akuini]

When you update specific customer data using the Userform, what information do you usually have at hand? I assumed the name and address. That's the reason why I included "address1" in the listbox.
Additionally, I noticed that you don't have information about the customer's phone number. May I ask why?

 

[Dan Swartz]

When you update specific customer data using the Userform, what information do you usually have at hand? I assumed the name and address. That's the reason why I included "address1" in the listbox.
Additionally, I noticed that you don't have information about the customer's phone number. May I ask why?

That's a great question. My initial purpose of this sheet did not require a phone number. but now that you ask, I'm guessing that would be a need I should plan for! Most generally, It will be just the address. but I think I should make provisions for a phone number!

 

[Dan Swartz]

@Akuini one thing I noticed on the workbook. during operation at some point. the headings end up being shuffled into the list. and the heading becomes a customer. I assume this has to do with filtering/sorting. but I would like the heading to remain in Row 2.

 

[Akuini]

I noticed on the workbook. during operation at some point. the headings end up being shuffled into the list. and the heading becomes a customer.

Ah, you're right, it should be 'Header:=xlYes' instead of 'Header:=xlNo'.
In Private Sub UserForm_Initialize, replace this part:

With tbl_CustomerDB.DataBodyRange
    .Sort Key1:=.Columns(2), Order1:=xlAscending, Key2:=.Columns(1), Order1:=xlAscending, Header:=xlNo

    va = tbl_CustomerDB.DataBodyRange.Columns(2).Value
         
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
End With

with this:

With tbl_CustomerDB.DataBodyRange
    .Sort Key1:=.Columns(2), Order1:=xlAscending, Key2:=.Columns(1), Order1:=xlAscending, Header:=xlYes

    va = tbl_CustomerDB.DataBodyRange.Columns(2).Value
         
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
End With

 

[Akuini]

The table is already messed up, so you need to manually get the header back to row 2.