Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- Mobile
Hi Guys,
I encounter the error when applying the code to avoid any duplicate value in a column with a dropdownlist created from VBA. Appreciate any help to rectify the error.
I encounter the error when applying the code to avoid any duplicate value in a column with a dropdownlist created from VBA. Appreciate any help to rectify the error.
VBA Code:
lr = Sheets("Data").Range("U2").End(xlDown).Row
strSortedAgt = "='Data'!$U$2:$U$" & lr
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strSortedAgt
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'Prevent Duplicate Name Input
If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
On Error GoTo ErrHandler:
If Not Intersect(Target, Range("A3:A" & lastRow)) Is Nothing Then
If WorksheetFunction.CountIf(Columns(Target.Column), Target.Value) > 1 Then
Application.DisplayAlerts = False
Target.ClearContents
Application.DisplayAlerts = True
MsgBox "AgentName already exists!"
End If
End If
ErrHandler:
Application.EnableEvents = True