Popup alert if value already exists in column when entering data through a UserForm

[thechad]

Greetings,

I am having trouble finding VBA code that suits my needs and am hoping someone can provide assistance.

I have a client profile worksheet that users populate through a UserForm. They enter first name and last name through 2 separate textboxes within the form along with some other required data. When they click on "Enter Profile", the data goes into a worksheet with the first and last being entered into separate columns on the same row (A & B). Column D uses formulas to join to names together in this format: last name, first name on each row. I would like the macro to go through Column D and if the client exists, generate a msgbox with a warning and then exit the sub.

Any assistance would be appreciated.

Thanks,
C

 

[SunnyAlv]

like this ?

Private Sub CommandButton1_Click()
    With Sheet1
    ls = .Cells(Rows.Count, 1).End(xlUp).Row
    
        If Application.CountIf(.Range("A1:A" & ls), TextBox1.Text) > 0 And Application.CountIf(.Range("B1:B" & ls), TextBox1.Text) > 0 Then
            MsgBox "Duplicate Name"
            Unload Me
        Else
            .Range("B" & Rows.Count).End(xlUp).Offset(1).Value = TextBox1.Text
            .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = TextBox2.Text
            .Range("B" & Rows.Count).End(xlUp).Offset(, 2).Value = Application.Concat(TextBox1.Value, " ", TextBox2.Value)
        End If
        
    End With
    
    Unload Me
    
End Sub