Error message in userform if duplicate entry

[Retroshift]

Hi, I have a data list and a userform to add additional row(s) with data. I would like an error message to pop up whenever duplicate data is entered in the according userform field on the basis of a specific column in a named table. A blank entry field is admitted, but no duplicate entries. I have a sub with code below but the code always triggers an error message saying that the entered value is a duplicate, even when it is not a duplicate. How can the code be altered so to only trigger an error message with real duplicate entries?

Private Sub txtShortName_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Set wsh = ThisWorkbook.Worksheets("List")
Set tbl = wsh.ListObjects("tblCls")
shortname = txtShortName.Value

    If shortname = "" Then Exit Sub
    If Not Application.WorksheetFunction.CountIf(tbl.DataBodyRange.Columns(3), shortname) > 1 And Len(shortname) > 0 Then
        txtShortName.Value = ""
        txtShortName.SetFocus
        Cancel = True
        MsgBox "The short name [" & shortname & "] already exists.", vbCritical, "DUPLICATE ENTRY"
    End If

End Sub

 

[Anthony47]

If Not Application.WorksheetFunction.CountIf(tbl.DataBodyRange.Columns(3), shortname) > 1 And Len(shortname) > 0 Then

I seem that the "Not" should be removed
Also the final "And etc etc" (in blue) may be omitted, as in case that shortname = "" the macro get terminated one line before

 

[Retroshift]

Hi Anthony, thanks for your reply. If I remove the "not" and the "and etc.", the userform data gets inserted in the table, ignoring if it is a duplicate or not. An error message does not pop up when it is a duplicate entry.

 

[Anthony47]

When the content of textbox txtShortName is written in column(3) of the table? Which is the code that do that?
Theoretically, if at the moment the code you attached it has not yet been written then the test should be done using >0 (instead than >1)

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Private Sub txtShortName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim wsh         As Worksheet
    Dim tbl         As ListObject
    Dim shortname   As String
    
    Set wsh = ThisWorkbook.Worksheets("List")
    Set tbl = wsh.ListObjects("tblCls")
    
    shortname = txtShortName.Value
    
    If Len(shortname) > 0 Then
        If Application.CountIf(tbl.DataBodyRange.Columns(3), shortname) > 0 Then
            txtShortName.Value = ""
            txtShortName.SetFocus
            Cancel = True
            MsgBox "The short name [" & shortname & "] already exists.", vbCritical, "DUPLICATE ENTRY"
        End If
    End If
    
End Sub

Dave

 

[Retroshift]

Thanks for your input, Anthony and Dave. Your solutions worked like a charm. Marked as solved.

 

[dmt32]

welcome glad we were able to help & appreciate your feedback

Dave