Private Sub CommandButton1_Click()
    Dim iRow As Long
    Dim rSearch As Range
    Dim rCl As Range
    Dim sFind As String
    Dim ws As Worksheet
    Set ws = Worksheets("Customers")

    
        'find first empty row in database
        iRow = ws.Cells(ws.Rows.Count, 2) _
               .End(xlUp).Offset(1, 0).Row
        Set rrng = ws.Range(ws.Cells(1, 1), ws.Cells(iRow, 5))

        'check for a filled fields
        If Trim(Me.TextName.Value) = "" Then
            Me.TextName.SetFocus
            MsgBox "Enter customer Name"
            Exit Sub
        End If
        If Trim(Me.TextVAT.Value) = "" Then
            Me.TextVAT.SetFocus
            MsgBox "Enter VAT Number"
            Exit Sub
        End If

        sFind = Trim(Me.TextName.Value)
         Set rSearch = ws.Range(ws.Cells(10, 5), ws.Cells(iRow, 5))
        Set rCl = rrng.Find(sFind, LookIn:=xlValues)
        If Not rCl Is Nothing Then
            MsgBox "Customer exists", vbCritical, "Duplicate entry"
            
           
            Exit Sub
            
        Else
            'copy the data to the database
            ws.Cells(iRow, 1).Value = Me.TextName.Value
            ws.Cells(iRow, 2).Value = Me.TextVAT.Value
            ws.Cells(iRow, 3).Value = Me.TextCountry.Value
            ws.Cells(iRow, 4).Value = Me.TextCity.Value
            ws.Cells(iRow, 5).Value = Me.TextAddress.Value
          
    End If
    
    'clear the data
    With Me
        .TextName.Value = ""
        .TextVAT.Value = ""
        .TextCountry.Value = ""
        .TextCity.Value = ""
        .TextAddress.Value = ""
        .TextName.SetFocus
    End With
End Sub