VBA: Msg Error if Duplicate

[PianoMan5]

Hello...As the title says, I want an error message to pop if the user inputs a value that's already listed in the same column.

I want the macro to run automatically once the user clicks out of the cell rather than having to run the macro manually so is there any way to do this? Below is what I have but the message box is not working and I haven't even started on putting in code to check whether the value is a duplicate. I'd love to have a custom form to direct the user to the duplicate value if one is listed...

Anyways, could someone help?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
'Entered into Trigger cell
If Target.Address = "B$:B$" Then
Set rTriggerCell = Target


MsgBox "This value is already listed; please ensure the new entry is valid", vbOKOnly

Application.EnableEvents = True

On Error GoTo 0
Exit Sub

End If

If Not rTriggerCell Is Nothing Then
Set rTriggerCell = Nothing
End If

Application.EnableEvents = True

On Error GoTo 0
End Sub

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If .Column = 2 Then

With .EntireColumn

Set cell = .Find(What:=Target.Value, AFter:=.Cells(1, 1))
If cell.Address = Target.Address Then

Set cell = .FindNext()
End If

If Not cell.Address = Target.Address Then

MsgBox "This value is already listed; please ensure the new entry is valid", vbOKOnly
End If
End With
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

[PianoMan5]

Perfect! I ended up using the OkCancel to provide an option to remove the value if the user chooses.

I've seen there is a Help button you can add to the Message box but how can I enter an action if the user selects that field?

Thanks!!

[Kenneth Hobs]

http://msdn.microsoft.com/en-us/library/Aa140542

[PianoMan5]

I tried that but the help field seems to only be useful if it references a URL and not something specific within excel.

I wanted the user to be able to select the help button and then the macro direct the user to the duplicate.

[Kenneth Hobs]

You can not change canned code like a MsgBox. If you want that, use another button like the OK button if you want to stick with MsgBox or even a custom userform.

To see what the return codes are for a MsgBox, press F1 in the VBE when your cursor is in or near the word MsgBox. You can even type MsgBox in the Immediate window and get help there or browse by F2.

[mikerickson]

Have you looked at DataValidation?
A formula like =(COUNTIF(A:A, A1)=1) with the Error alert style:Information does what you want.