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

 

[Rick Rothstein]

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

If there are two or more of the same name, where should those "extra" names be placed, under or alongside the first one that is found?

 

[Dan Swartz]

At this point, I have lots of options. I'm just in the middle of creating it. I would prefer them to show up in a popup box and after I choose the correct customer, it disappears and fills out my form. but that may be too fancy. If it needs to be a listbox that always shows. that's ok. Attached is what my Userform looks like.

 

[Rick Rothstein]

Well, if that is your form, you would need some kind of up/down button system so you could move through the multiple names when there are multiple names.

 

[Dan Swartz]

Well, if that is your form, you would need some kind of up/down button system so you could move through the multiple names when there are multiple names.

I'm open to any suggestions you would have. I'm very new to VBA. The people using this will be computer illiterate. they will struggle with complexity. So I want to make it easy to use. Again. I can modify the form for whatever is best. Not stuck on that. just what I created to this point

 

[Akuini]

@Dan Swartz
I just want to suggest something:
I hope you have a column for a unique ID for each customer in your data table. If not, create one. It should be something like this:

Book1
	A	B	C
1	CUSTOMER ID	NAME	ADDRESS
2	A000001	Helen	adress1
3	A000002	David	adress2
4	A000003	David	adress3
5	A000004	Ronald	adress4
6	A000005	Kimberly	adress5
7	A000006	Maria	adress6
Sheet2

Please ensure that the number of digits will be sufficient to accommodate all future customers.

 

[Dan Swartz]

@Dan Swartz
I just want to suggest something:
I hope you have a column for a unique ID for each customer in your data table. If not, create one. It should be something like this:

Book1
	A	B	C
1	CUSTOMER ID	NAME	ADDRESS
2	A000001	Helen	adress1
3	A000002	David	adress2
4	A000003	David	adress3
5	A000004	Ronald	adress4
6	A000005	Kimberly	adress5
7	A000006	Maria	adress6
Sheet2

Please ensure that the number of digits will be sufficient to accommodate all future customers.

I have everything in columns like that. but I do not have a Customer ID. I don't necessarily need on. but if it's better to use in this case. then I can add that.

 

[Akuini]

I have everything in columns like that. but I do not have a Customer ID. I don't necessarily need on. but if it's better to use in this case. then I can add that.

Having a unique identifier for each record is a basic requirement in setting up a proper database. It makes data management and coding much easier. For example, after editing a record in the user form, you only need to identify the customer ID to update the correct record.
So, yeah, it's better to have one.

 

[Dan Swartz]

Having a unique identifier for each record is a basic requirement in setting up a proper database. It makes data management and coding much easier. For example, after editing a record in the user form, you only need to identify the customer ID to update the correct record.
So, yeah, it's better to have one.

Thank You. Already in the process of adding! thank you for your suggestion!!!!

 

[Akuini]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.