Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking up duplicates | Excel Discussion (Misc queries) | |||
Duplicates | Excel Worksheet Functions | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
Duplicates in excel that aren't 100% DUPLICATES ... | Excel Discussion (Misc queries) | |||
VBA - Do not allow duplicates | Excel Worksheet Functions |