PianoMan5
01-18-2012, 09:46 AM
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
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