No duplicates
 

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

No duplicates
 

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

No duplicates
 

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" skrev i melding
...
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

No duplicates
 

Thanks for the replies. All working well.

Regards
Greg

"Harald Staff" wrote in message
...
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" skrev i melding
...
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