I would like to implement a security check for duplicated records as follows:

1. Notify user, if he / she adds an ID, which already exists in the table (same sheet)
2. Notify user, if he / she adds an ID, which already exists in other files (data loaded as ListObject via PowerQuery)

The below script should take care of the above points. It works fine for the 1st point and notify user, if he / she adds ID, which appears more than once. However it does nothing, if user adds an ID, which appears already on the sheet "Data", in the list object "Data" and column "ID"

Private Sub Worksheet_Change(ByVal target As Range)

Dim count As Long
'1. This checks, whether the ID already exists in the same sheet and appears more than once
 If target.Column = 2 Then
        
        If target.Cells.count = 1 Then
                count = Application.WorksheetFunction.CountIf(Tracker.ListObjects(1).ListColumns("ID").DataBodyRange, target.Value)
                
                If count > 1 Then
                    MsgBox "WARNING: ID " & target.Value & " already exists in the table."
                End If
        End If
    '2. This checks, whether the ID already exists in the other files
    Else
        If target.Cells.count > 0 Then
                count = Application.WorksheetFunction.CountIf(Data.ListObjects("Data").ListColumns("ID").DataBodyRange, target.Value)
                
                If count > 0 Then
                    MsgBox "WARNING: ID " & target.Value & " already exists in other trackers."
                End If
        End If
        
   End If
    
End Sub

Any ideas or suggestions, what's wrong, please?

CodePudding user response：

As far as I understand your code, the ID is entered in column 2, however your check against the ListObject is done only if something was entered in any other column (because you put the check in the Else-branch). I guess the following should do the trick

Private Sub Worksheet_Change(ByVal target As Range)
    Dim count As Long
    If target.Column <> 2 Then Exit Sub
    If target.Cells.count <> 1 Then Exit Sub

    '1. This checks, whether the ID already exists in the same sheet and appears more than once
    count = Application.WorksheetFunction.CountIf(Tracker.ListObjects(1).ListColumns("ID").DataBodyRange, target.Value)      
    If count > 1 Then
        MsgBox "WARNING: ID " & target.Value & " already exists in the table."
        Exit Sub
    End If

     ' 2. This checks, whether the ID already exists in the other files
    count = Application.WorksheetFunction.CountIf(Data.ListObjects("Data").ListColumns("ID").DataBodyRange, target.Value)       
    If count > 0 Then
        MsgBox "WARNING: ID " & target.Value & " already exists in other trackers."
    End If
End Sub

I strongly recommend to learn how to use the debugger - such problems are usually easy to detect if you execute the code step by step.