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:
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:
VBA 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
Last edited by a moderator: