Checking for MULTIPLE duplicates on save_click

[JaxVen]

Hello all

I haven't been working with VBA very long and have been searching for a way to check a table within a worksheet for duplicates on saving a form.

I have found some methods and tried them, but have been unable to figure out how to check multiple.

Basically, I need to check both text boxes within the form: Name1TEXT (first name) and Name2TEXT (surname) for duplicates, which are in columns A and B within my table (NewUserTable on Sheet7). I need to prevent the same user being entered twice and unfortunately the data must have the name separated into first name and surname (would make this a lot easier if I could have one text box for full name, but I have a LOT of data being pulled from this table elsewhere that needs it to be separate). Which of course means I have to check both name fields for duplicates, there are no other identifiers. I appreciate any help anyone can offer!

Here is my current code for the form;

Private Sub Save_Click()

    'Registration details
    
    Dim LastRow As Range
    Dim UserTable As ListObject


    'Add row to bottom of New user table
    Sheet7.ListObjects("NewUserTable").ListRows.Add
    
    'Enter data from form into new row at bottom
    Set UserTable = Sheet7.ListObjects("NewUserTable")
    Set LastRow = UserTable.ListRows(UserTable.ListRows.Count).Range
 
With LastRow
 
    .Cells(1, 1) = Name1TEXT.Value
    .Cells(1, 2) = Name2TEXT.Value
    .Cells(1, 3) = GenderCOMBO.Value
    .Cells(1, 4) = PostcodeTEXT.Value
    .Cells(1, 8) = Contact1TEXT.Value
    .Cells(1, 9) = Contact2TEXT.Value
    .Cells(1, 10) = DateRegTEXT.Value
    .Cells(1, 11) = GroupCOMBO.Value
    .Cells(1, 13) = EmpCOMBO.Value
    .Cells(1, 19) = MedicalDetailsTEXT.Value
    .Cells(1, 20) = EthnicityCOMBO.Value
    .Cells(1, 21) = ReligionCOMBO
    .Cells(1, 22) = SexualityCOMBO
    .Cells(1, 24) = AgeCOMBO
          
    'result for needs 1 checkbox
    If PhysDysCHECK.Value = True Then
    
        .Cells(1, 14) = "Physical Disability"
        
    Else
    
        .Cells(1, 14) = "None"
        
    End If
        
    'result for needs 2 checkbox
    If LearnDiffCHECK.Value = True Then
    
        .Cells(1, 15) = "Learning Difficulty"
        
    Else
    
        .Cells(1, 15) = "None"
        
    End If
        
    'result for needs 3 checkbox
    If MentalHCHECK.Value = True Then
    
        .Cells(1, 16) = "Mental Health"
        
    Else
    
        .Cells(1, 16) = "None"
        
    End If
         
    'result for needs 4 checkbox
    If PhysHCHECK.Value = True Then
    
        .Cells(1, 17) = "Physical Health"
        
    Else
    
        .Cells(1, 17) = "None"
        
    End If
        
    'result for allergies checkbox
    If AllergiesCHECK.Value = True Then
    
        .Cells(1, 18) = "Yes"
        
    Else
    
        .Cells(1, 18) = "No"
        
    End If
     
End With


End Sub

[SamT]

I haven't worked with UserTables before, but this makes sense. Kinda.

Dim Rw As Range

For each Rw in UserTable.ListRows
  If Rw .Cells(1) = Name1TEXT and .Cells(2) = Name2TEXT Then
    MsgBox "Duplicate Name(s) Found in Row " & Rw.Row
  End If
Next Rw

[JaxVen]

I haven't worked with UserTables before, but this makes sense. Kinda.

Dim Rw As Range

For each Rw in UserTable.ListRows
  If Rw .Cells(1) = Name1TEXT and .Cells(2) = Name2TEXT Then
    MsgBox "Duplicate Name(s) Found in Row " & Rw.Row
  End If
Next Rw

Thanks for reply - tried today and unfortunately doesnt work. After a little debugging I 'think' the correct code is as below but it wont work with current code. I have tried both before and after.

Dim Rw As Range


For Each Rw In NewUserTable.ListRows
  If Rw.Cells(1) = Name1TEXT.Value And Rw.Cells(2) = Name2TEXT.Value Then
    MsgBox "This user already exists, please use search to update their record" & Rw.Row
  End If
Next Rw