clearing a duplication from excel sheet

[GBDavis]

I am trying to get the duplicated input removed. I have a input form that a user can input a new serial number, however if it is a duplicate I need the program to remove it. The code I am using is as follows:

Public clr As Boolean
Public eRow As Long
Public dup As String
Private Sub worksheet_change(ByVal Target As Range)
Dim dm As Long

   If Application.CountIf(Range("A:A"), Target) > 1 Then
    MsgBox "duplicate Serial Number!  Not Allowed!!!", vbCritical, "removing your input"
  dup = "true"
   
   
   End If
   dup = "False"
End Sub

I need to have the dim "dup" to pass either true or false to another operation for the input screen.

here is the code I am using for the input screen:

Private Sub CommandButton1_Click()
Dim unusedRow As Long
'Dim p As Long

'place sn into next row

eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
eRow2 = eRow
MsgBox (dup), vbOKCancel

If dup = "true" Then
Call No_dup
End If
'Start with first textbox (New Serial Number)
nsn.SetFocus
Sheet2.Cells(eRow, 1).Value = nsn.Value
Me.nsn = nsn.Value

'Option button choices
If OptionButton1 = True Then
tv = "25 in/lbs +/- 6%"
ElseIf OptionButton2 = True Then
tv = "35 in/lbs +/- 6%"
ElseIf OptionButton3 = True Then
tv = "55 in/lbs +/- 6%"
ElseIf OptionButton4 = True Then
tv = "65 in/lbs +/- 6%"
End If
'end of choices

'place choice in to 2nd column(torque value +/- 6%)
Sheet2.Cells(eRow, 2).Value = tv

'Place the data for Modle Number in the appropriate row and column
Sheet2.Cells(eRow, 3).Value = mn.Value

 'UserForm2.Hide
' UserForm1.Show

End Sub

Private Sub CommandButton2_Click()
Call CMD_Cancel_Click

End Sub
Private Sub CMD_Cancel_Click()
If MsgBox("Do you really want to close?", vbOKCancel) = vbOK Then
ActiveWorkbook.Save
Application.Quit

End If
'End Sub
'Me.Hide
'UserForm1.Show

End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, closemode As Integer)
If closemode = vbFormControlMenu Then

Cancel = True
Call CMD_Cancel_Click
End If
End Sub
'Private Sub clear_row()
'Sheet2.Cells(eRow - 1, 1).Value = ""
'Sheet2.Cells(eRow - 1, 2).Value = ""
'Sheet2.Cells(eRow - 1, 3).Value = ""
'End Sub
Private Sub UserForm_Initialize()

End Sub
Private Sub No_dup()
MsgBox ("made it to del part"), vbOKCancel

Sheet2.Cells(eRow, 1).Value = ""
Sheet2.Cells(eRow, 2).Value = ""
Sheet2.Cells(eRow, 3).Value = ""
End Sub

any help would be greatly appreciated

 

[DanteAmor]

I am trying to get the duplicated input removed. I have a input form that a user can input a new serial number, however if it is a duplicate I need the program to remove it. The code I am using is as follows:

The recommendation is to validate the number before putting it on the sheet.

Private Sub CommandButton1_Click()
    Dim eRow, tv As String, f As Range
    
    'VALIDATIONS
    If nsn.Value = "" Then
        MsgBox "Capture nsn"
        Exit Sub
    End If
[COLOR=#ff0000]    Set f = Sheet2.Range("A:A").Find(nsn.Value, LookIn:=xlValues, lookat:=xlWhole)[/COLOR]
[COLOR=#ff0000]    If Not f Is Nothing Then[/COLOR]
[COLOR=#ff0000]        MsgBox "nsn Duplicate"[/COLOR]
[COLOR=#ff0000]        Exit Sub[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
    
    'Option button choices
    If OptionButton1 = True Then
        tv = "25 in/lbs +/- 6%"
    ElseIf OptionButton2 = True Then
        tv = "35 in/lbs +/- 6%"
    ElseIf OptionButton3 = True Then
        tv = "55 in/lbs +/- 6%"
    ElseIf OptionButton4 = True Then
        tv = "65 in/lbs +/- 6%"
    End If
    'end of choices
    
    eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Sheet2.Cells(eRow, 1).Value = nsn.Value
    Sheet2.Cells(eRow, 2).Value = tv
    Sheet2.Cells(eRow, 3).Value = mn.Value
    nsn.SetFocus
End Sub

 

[GBDavis]

That worked. Thank you very much.

Glynn

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.