No duplicates

[gregork]

I have a textbox on a userform I use to input data on to a worksheet. The
data is a seven digit number and I want to ensure I don't have any
duplicates of the number entered. Is there a way I can achieve this using
code? Here is the code I have so far:

Dim LastRow As Object

Set LastRow = Worksheets("Blends Produced").Range("A6000").End(xlUp)

LastRow.Offset(1, 0).Value = Me.TextBox1.Value


Cheers
Greg

 

[Sharad]

Put this in your TextBox change or, Command button click proedure (which
ever you are using to put the data in to the sheet.)

With Worksheets("Blends Produced")
For Each c In .Range("A1:A" & .Range("A1").End(xlDown).Row).Cells
If c.Value = Textbox1.Value Then
MsgBox "The value you entered already exists." _
& Chr(13) & "Please enter another value."
Textbox1.Value = ""
Textbox1.SetFocus
Exit For
End If
Next c
End With

 

[Harald Staff]

Hi Greg

Private Sub CommandButton1_Click()
Dim LastRow As Range
If Duplicate(TextBox1.Text) = False Then
Set LastRow = Worksheets("Blends Produced").Range("A6000").End(xlUp)
LastRow.Offset(1, 0).Value = Me.TextBox1.Value
Me.TextBox1.Text = ""
Me.TextBox1.SetFocus
Else
MsgBox TextBox1.Text & " is already in the list."
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
TextBox1.SetFocus
End If
End Sub

Private Function Duplicate(Entry As String) As Boolean
Dim Found As Range
On Error Resume Next
Set Found = Worksheets("Blends Produced").Columns(1).Find(What:=Entry, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not Found Is Nothing Then Duplicate = True
End Function

HTH. Best wishes Harald

 

[gregork]

Thanks for the replies. All working well.

Regards
Greg