Userform command button Duplicates -- Please Help

[abbas50]

I want a duplicate entry to column H to be prevented i have thousands of Rows and its hard to go throu each one please help cant figure it out

Private Sub CommandButton1_Click()
Dim cNum As Integer
Dim x As Integer
Dim nextrow As Range
Dim sht As String
'set the variable for the sheet
sht = ComboBox1.Value
'check for values
If Me.ComboBox1.Value = "" Then
MsgBox "Select a sheet from the combobox and add the date"
Exit Sub
End If
'change the number for the number of controls on the userform
cNum = 15
'add the data to the selected worksheet
Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
For x = 1 To cNum
nextrow = Me.Controls("TextBox" & x).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear the values in the userform
For x = 1 To cNum
Me.Controls("TextBox" & x).Value = ""
Next
'communicate the results
MsgBox "The values have been sent to the " & sht & " sheet"

Dim arrItems()
Dim cnt As Long
Dim pro As Long

For pro = 0 To TextBox3.ListCount - 1
If TextBox3.Selected(pro) Then
ReDim Preserve arrItems(cnt)
arrItems(cnt) = TextBox3.List(pro)
cnt = cnt + 1

End If
Next pro

If cnt > 0 Then
Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")
End If


End Sub

 

[DanteAmor]

Check if this is what you need.

Private Sub CommandButton1_Click()
  Dim cNum As Integer, x As Integer, nextrow As Range, sht As String
  Dim f As Range
  'set the variable for the sheet
  sht = ComboBox1.Value
  'check for values
  If Me.ComboBox1.Value = "" Then
    MsgBox "Select a sheet from the combobox and add the date"
    Exit Sub
  End If
 
  'Prevent duplicates in column H
  Set f = Sheets(sht).Range("H:H").Find(TextBox8.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    MsgBox "An entry in column H already exists: " & TextBox8.Value
    Exit Sub
  End If
 
  'change the number for the number of controls on the userform
  cNum = 15
  'add the data to the selected worksheet
  Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  For x = 1 To cNum
    nextrow = Me.Controls("TextBox" & x).Value
    Set nextrow = nextrow.Offset(0, 1)
  Next
  'clear the values in the userform
'  For x = 1 To cNum
'    Me.Controls("TextBox" & x).Value = ""
'  Next
  'communicate the results
  MsgBox "The values have been sent to the " & sht & " sheet"
 
'  Dim arrItems()
'  Dim cnt As Long
'  Dim pro As Long
'
'  For pro = 0 To TextBox3.ListCount - 1
'    If TextBox3.Selected(pro) Then
'      ReDim Preserve arrItems(cnt)
'      arrItems(cnt) = TextBox3.List(pro)
'      cnt = cnt + 1
'    End If
'  Next pro
'
'  If cnt > 0 Then
'    Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")
'  End If
End Sub

I commented all this part, because you have a textbox, I suppose it must be a listbox.

'  Dim arrItems()
'  Dim cnt As Long
'  Dim pro As Long
'
'  For pro = 0 To TextBox3.ListCount - 1
'    If TextBox3.Selected(pro) Then
'      ReDim Preserve arrItems(cnt)
'      arrItems(cnt) = TextBox3.List(pro)
'      cnt = cnt + 1
'    End If
'  Next pro
'
'  If cnt > 0 Then
'    Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")
'  End If

 

[abbas50]

Thank you so much .. Much appreciated this is what i want exactly !!!!

one last thing i have something with my code above if you know how to solve and i will finish my task :/

'  Dim arrItems()
'  Dim cnt As Long
'  Dim pro As Long
'
'  For pro = 0 To TextBox3.ListCount - 1
'    If TextBox3.Selected(pro) Then
'      ReDim Preserve arrItems(cnt)
'      arrItems(cnt) = TextBox3.List(pro)
'      cnt = cnt + 1
'    End If
'  Next pro
'
'  If cnt > 0 Then
'    Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")
'  End If

i named my ListBox TextBox3 now when i open my data form entry the Listbox which is called Textbox3 it goes correctly with the multi selection but it doesnt go to the specified sheet it goes at the sheet i am at :/ how can i solve this please have you got any idea if you want my file to look at it i can email it to you ty so much its been 6 days and its my third time to do any coding im trying to learn as well ty so much for your time

Check if this is what you need.

Private Sub CommandButton1_Click()
  Dim cNum As Integer, x As Integer, nextrow As Range, sht As String
  Dim f As Range
  'set the variable for the sheet
  sht = ComboBox1.Value
  'check for values
  If Me.ComboBox1.Value = "" Then
    MsgBox "Select a sheet from the combobox and add the date"
    Exit Sub
  End If

  'Prevent duplicates in column H
  Set f = Sheets(sht).Range("H:H").Find(TextBox8.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    MsgBox "An entry in column H already exists: " & TextBox8.Value
    Exit Sub
  End If

  'change the number for the number of controls on the userform
  cNum = 15
  'add the data to the selected worksheet
  Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  For x = 1 To cNum
    nextrow = Me.Controls("TextBox" & x).Value
    Set nextrow = nextrow.Offset(0, 1)
  Next
  'clear the values in the userform
'  For x = 1 To cNum
'    Me.Controls("TextBox" & x).Value = ""
'  Next
  'communicate the results
  MsgBox "The values have been sent to the " & sht & " sheet"

'  Dim arrItems()
'  Dim cnt As Long
'  Dim pro As Long
'
'  For pro = 0 To TextBox3.ListCount - 1
'    If TextBox3.Selected(pro) Then
'      ReDim Preserve arrItems(cnt)
'      arrItems(cnt) = TextBox3.List(pro)
'      cnt = cnt + 1
'    End If
'  Next pro
'
'  If cnt > 0 Then
'    Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")
'  End If
End Sub

I commented all this part, because you have a textbox, I suppose it must be a listbox.

'  Dim arrItems()
'  Dim cnt As Long
'  Dim pro As Long
'
'  For pro = 0 To TextBox3.ListCount - 1
'    If TextBox3.Selected(pro) Then
'      ReDim Preserve arrItems(cnt)
'      arrItems(cnt) = TextBox3.List(pro)
'      cnt = cnt + 1
'    End If
'  Next pro
'
'  If cnt > 0 Then
'    Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")
'  End If

 

[DanteAmor]

First, you should not name a listbox as textbox, if you want to put the name: "Listbox3".
In your textbox relationship, I think you already have a textbox3.
You should not cause yourself conflicts.

  For x = 1 To cNum
    nextrow = Me.Controls("TextBox" & x).Value
    Set nextrow = nextrow.Offset(0, 1)
  Next

In the previous code, you go through the "TextBox3" control.

---------------------------------------

Try this

Sheets(sht).Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")

 

[abbas50]

Becaue i had a me.controls i renamed my listbox to textbox3 and i dont have textbox3 im sure of it ty for your help i will learn from my mistakes

Your 2 solutions worke like a charm i cant thank you enough ty so much from the bottom of my heart appreciated :D you made me so happy

ty Mate

First, you should not name a listbox as textbox, if you want to put the name: "Listbox3".
In your textbox relationship, I think you already have a textbox3.
You should not cause yourself conflicts.

  For x = 1 To cNum
    nextrow = Me.Controls("TextBox" & x).Value
    Set nextrow = nextrow.Offset(0, 1)
  Next

In the previous code, you go through the "TextBox3" control.

---------------------------------------

Try this

Sheets(sht).Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")

 

[DanteAmor]

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