found duplicate value in textbox and dont allow to insert column A

[mrchesser]

Hello, I wrote this code and it checks in the text box that there is no duplicate data to be added and it works correctly (the input values in the text box are multiple and entered below each other)
I want to have a code that checks if the value already exists in column A and shows it and does not just add the same value and adds the rest.
tank u

Private Sub CommandButton3_Click()
    Dim Target As Range
    Dim Data As Variant
    Dim DuplicateNumbers As String
    Dim i As Long, j As Long
    Dim DuplicateFound As Boolean
    
    If TextBox1.Text = "" Then Exit Sub
    Data = Split(TextBox1.Text, vbCrLf)
    
    With Worksheets("sheet1")
        Set Target = .Range("A" & .Rows.Count).End(xlUp)
        If Target.Value <> "" Then Set Target = Target.Offset(1)
        
        ' Check for duplicate numbers
        For i = LBound(Data) To UBound(Data)
            DuplicateFound = False
            For j = i + 1 To UBound(Data)
                If Data(i) = Data(j) Then
                    DuplicateNumbers = DuplicateNumbers & Data(i) & vbCrLf
                    DuplicateFound = True
                    Exit For
                End If
            Next j
            If DuplicateFound = False Then
                Target.Value = Data(i)
                Set Target = Target.Offset(1)
            End If
        Next i
    End With
    
    ' Display duplicate numbers
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers:" & vbCrLf & DuplicateNumbers
    End If
End Sub

Book1 (1).xlsm
	A	B	C	D
1	number
2	2
3	3
4	4
5	5
6	43
7	44
8	2
9	2
10	2
11	3
12	3
13
Sheet1

 

[mrchesser]

@Flashbond​

If you can, for post number 9
which is for archiving, help me
It is clear in the figure that the numbers are entered and before click insert button , the number of the cabinet and its drawer is selected

And then after click on insert button the name of the cabinet(textbox) in column B and the select of its drawer(option button ) in column C corresponding to the entered numbers.
tank u very much

 

[Legacy 143009]

Try:

Private Sub CommandButton3_Click()
    Dim Target As Range
    Dim Data As Variant
    Dim DuplicateNumbers As String
    Dim i As Long, j As Long
    Dim DuplicateFound As Boolean
    Dim duplicates As String
    Dim drawer As String
    Dim d As Control

     ' Loop through ALL the controls on the UserForm.
     For Each d In Me.Controls
         ' Check to see if "Option" is in the Name of each control.
         If InStr(d.Name, "Option") Then
             ' Check Group name.
             If d.GroupName = "" Then
                 ' Check the status of the OptionButton.
                 If d.Value = True Then
                     drawer = d.Caption
                     Exit For
                 End If
             End If
         End If
     Next
    
    If TextBox1.Text = "" Then Exit Sub
    Data = Split(TextBox1.Text, vbCrLf)
    
    With Worksheets("sheet1")
        Set Target = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
        
        ' Check for duplicate numbers
        For i = LBound(Data) To UBound(Data)
            Data(i) = Trim(Data(i))
            DuplicateFound = False
            
            ' Check for duplicate numbers
            For j = i + 1 To UBound(Data)
                If Data(i) = Trim(Data(j)) Then
                    DuplicateNumbers = DuplicateNumbers & Data(i) & vbCrLf
                    DuplicateFound = True
                    Exit For
                End If
            Next j
            
            If Not DuplicateFound Then
                If Application.CountIf(.Range("A:A"), Data(i)) = 0 Then
                    Target.Value = Data(i)
                    Target.Offset(, 1) = "cabinet " & Trim(TextBox2.Text)
                    Target.Offset(, 2) = drawer
                    Set Target = Target.Offset(1)
                Else
                    duplicates = duplicates & Data(i) & ", "
                End If
            End If
        Next i
        
        ' Display duplicates message box if any
        If Len(duplicates) > 0 Then
            duplicates = Left(duplicates, Len(duplicates) - 2)
            
            Select Case UBound(Split(duplicates, ","))
                Case 0
                    MsgBox duplicates & " is already in the sheet."
                Case Else
                    MsgBox duplicates & " are already in the sheet."
            End Select
        End If
    End With
    
    ' Display duplicate numbers message box if any
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers in Form:" & vbCrLf & DuplicateNumbers
    End If
End Sub

 

[mrchesser]

@Flashbond​

excellent
You are a genius
It works great
If it is possible to highlight the row in sheet that is duplicate, I would be very grateful
If it's a lot of trouble, it doesn't matter, thanks again

 

[Legacy 143009]

highlight the row in sheet that is duplicate

With this system there will be no duplicates in the sheet. I didn't understand your question?

 

[mrchesser]

@Flashbond​

It is true that duplicate data is not created
But if the number entered in the text box is equal to one of the numbers in column A, I want that number to be highlighted in column A.


You can give an explanation about this code
Why is it repeated once by is and once by are?

 Select Case UBound(Split(duplicates, ","))
                Case 0
                    MsgBox duplicates & " is already in the sheet."
                Case Else
                    MsgBox duplicates & " are already in the sheet."
            End Select

If enter after type last number
and the cursor blinks
It gives this message


can u fix this ? Give only one type of message, even if the flashing cursor is one line down after the last number in the text box.
tank u again

 

[Legacy 143009]

Here is a completely different approach from me according to your needs. In my opinion, it is more understandable and easy to modify:

Private Sub CommandButton3_Click()
  Dim numberList As Object, a As Object, numbers As Variant, number As Variant
  Dim duplicates As String, drawer As String
  Set a = Application
  Set numberList = CreateObject("Scripting.Dictionary")
  Dim d As Control
  numbers = Split(TextBox1.Text, vbCrLf)
 
  'Loop through ALL the controls on the Form to get drawer name.
  For Each d In Me.Controls
    'Check to see if "Option" is in the Name of each control.
    If InStr(d.Name, "Option") Then
      'Check the status of the OptionButton.
      If d.Value = True Then
        drawer = d.Caption
        Exit For
      End If
   End If
  Next
 
  'Make unique numberlist from Form
  For Each number In numbers
    If Trim(number) <> "" Then
      If Not numberList.Exists(Trim(number)) Then
        numberList.Add Trim(number), 1
      Else
        'If it exists in dictionary, add to duplictes
        duplicates = duplicates & Trim(number) & vbCrLf
      End If
    End If
  Next
 
  'Display message for Form duplicates
  If Len(duplicates) > 0 Then
    MsgBox "Duplicates on the Form: " & vbCrLf & duplicates
  End If
 
  duplicates = ""
  'Write to Sheet
  With Worksheets("sheet1")
  'Clear previous marked cells
  .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Interior.Color = xlNone
  For Each number In numberList
    'Check if the number is in range. If error, add to range.
    If IsError(a.Match(CDbl(number), .Range("A:A"), 0)) Then
      'If not match, wtire
      With .Range("A" & .Rows.Count).End(xlUp)
        .Offset(1).Value = number
        .Offset(1, 1).Value = "cabinet " & Trim(TextBox2.Text)
        .Offset(1, 3).Value = drawer
      End With
    Else
      'If it is found, then add to duplicates
      duplicates = duplicates & number & vbCrLf
      'Mark duplicate rows
      .Range("A" & a.Match(CDbl(number), .Range("A:A"), 0)).Interior.ColorIndex = 6
    End If
  Next
  End With
 
    'Display message for Sheetduplicates
  If Len(duplicates) > 0 Then
    MsgBox "Duplicates on the sheet: " & vbCrLf & duplicates
  End If
End Sub

 

[abrahamdany20]

@Flashbond​

tank u bro
can you fix this for just number to be add in text box?
If a word is accidentally entered, it will crash