Deny duplicates in dropdown lists using VBA

[Moe12345]

Hi, I am currently experiencing problems with duplicates in dependent dropdown lists. I have sheet with 6 lists, the second list is dependent on the first
and the remaining lists are all dependent on the entry of the second list. These lists are all suppose to reset and does show "Please select..." when a new entry is made in the second
and first list. The problem arises when I use a code the disallows duplicates. Then the reset functionality is not working. Could anyone please help me with this to make all of it work,
ie both the reset function and denying duplicates?

This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)


  If Target.Address = "$E$4" Then
   
   
Range("E6").Value = "Please Select..."
End If


  If Target.Address = "$E$6" Then
 
Range("C11:C20").Value = "Please Select..."
Application.EnableEvents = True

End If



If Target.Address = "$E$6" Then
Range("C26:c30").Value = "Please Select..."

End If

If Target.Address = "$E$6" Then
Range("C35:c37").Value = "Please Select..."
 
 End If
 
 
If Target.Address = "$E$6" Then
Range("C43").Value = "Please Select..."

End If

If Intersect(Target, Range("C10:c43")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If WorksheetFunction.CountIf(Range("C10:C43"), Target) > 1 Then
Application.EnableEvents = False
Application.Undo                           'This part gives an error
If Application.EnableEvents = True Then
MsgBox "Item already selected, please try again."
End If

 

[Snakehips]

@Moe12345 I obviously don't fully appreciate your need but, does this help?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$4" Then
    Range("E6").Value = "Please Select..."
End If


If Target.Address = "$E$6" Then
    Application.EnableEvents = False
    Range("C11:C20, C26:c30, C43").Value = "Please Select..."
    Application.EnableEvents = True
End If


If Intersect(Target, Range("C10:c43")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If WorksheetFunction.CountIf(Range("C10:C43"), Target) > 1 Then
        Prev = Target
        Application.EnableEvents = False
        If Application.EnableEvents = False Then
        MsgBox Prev & " already selected, please try again."
        Application.Undo
    End If
End If
Application.EnableEvents = True
End Sub

 

[Moe12345]

Thank you for the reply, i've tested the code and it seems to be working perfectly. A huge thank you from me!

 

[Moe12345]

hey @Snakehips, do you think you could help me with my new problem in this thread Need Help with making listboxes visible based on the selection of dropdownlist (vba)

That would conclude my excel project, any help would be greatly appreciated.