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

Status
Not open for further replies.
M

mrchesser

Banned User
Joined
Oct 24, 2023
Messages
9
Office Version
  1. 2019
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

VBA Code: 
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
ABCD
1number
22
33
44
55
643
744
82
92
102
113
123
13
Sheet1
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi,

This should work:
VBA Code: 
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
    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
              If IsError(Application.Match(CDbl(Data(i)), .Range("A:A"), 0)) Then
                Target.Value = Data(i)
                Set Target = Target.Offset(1)
              Else
                duplicates = duplicates & Data(i) & ", "
              End If
            End If
           
        Next i
        duplicates = Left(duplicates, Len(duplicates) - 2)
        MsgBox duplicates & " are duplicate values."
    End With
   
    ' Display duplicate numbers
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers:" & vbCrLf & DuplicateNumbers
    End If
End Sub
 
Upvote 0

@Flashbond​

If we repeat the new number twice in the form, I get an error. Can you fix it?TANK U
Is it possible to write the code so that no data is placed in column A until duplicate data is deleted from column A or from inside the text box?
 
Upvote 0
I made small modifications but the code is working totally fine for me.
I start like below
1698762037651.png

First warning is like this, no problem:
1698762067578.png

Second warning is like this, again no problem:
1698762104471.png

Please find the latest code below:
VBA Code: 
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
    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
              If IsError(Application.Match(CDbl(Data(i)), .Range("A:A"), 0)) Then
                Target.Value = Data(i)
                Set Target = Target.Offset(1)
              Else
                duplicates = duplicates & Data(i) & ", "
              End If
            End If
        
        Next i
        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
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers in Form:" & vbCrLf & DuplicateNumbers
    End If
End Sub
Please share a sample scenario if you are still having problems.
 
Upvote 0

@Flashbond barvo man that is work​


If a space is added after entering the numbers in the text box and an empty space is created, it will give error 13
Another issue
if i want after entering the numbers in textbox and When pressing the button, the form2 opens, and the caption of that button1 which is in form2 is placed in front of the numbers entered in column B. What should I do?
 
Upvote 0
mrchesser said:
and the caption of that button1 which is in form2 is placed in front of the numbers entered in column B.
Click to expand...
I was not able to imagine this in my mind. Can you share a screenshot please?

For the other issue try this:
VBA Code: 
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
    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)
            Data(i) = Trim(Data(i))
            DuplicateFound = False
            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 DuplicateFound = False Then
              If IsError(Application.Match(CDbl(Data(i)), .Range("A:A"), 0)) Then
                Target.Value = Data(i)
                Set Target = Target.Offset(1)
              Else
                duplicates = duplicates & Data(i) & ", "
              End If
            End If
        Next i
        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
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers in Form:" & vbCrLf & DuplicateNumbers
    End If
End Sub
 
Upvote 0

@Flashbond tank u for answer bro​

i have 3 form(1,2,3)
after insert number in textbox1 and prees insert button i want to show form2 to select one of the cabinet that set in (range of textbox number) in column B

and after show form3 to select one of the drawer to insert in (range of textbox number) column C
Because it is possible to enter duplicate numbers, it is better that if there is a duplicate number, no value should be added in column A, and when there is no duplicate data message, form 2 and form 3 should be shown in order to determine the location (cabinet and drawer) for the entered numbers.
 

Attachments

  • form2.jpg
    form2.jpg
    34.5 KB · Views: 5
  • form3.jpg
    form3.jpg
    35.5 KB · Views: 5
  • sheet.jpg
    sheet.jpg
    106.4 KB · Views: 4
Upvote 0

@Flashbond​

this is better if you can help me
in text box front cabinet in column B
and the option button in column C

if you click on the picture can downland my file

archive.jpg
 
Upvote 0
Status
Not open for further replies.

Similar threads

R
  • Solved
values in column C must be larger than in B
Replies
4
Views
182
robertvdb
R
A
  • Question
Moving Rows Up (Scroll) To Defined Row Number for Viewing
Replies
6
Views
222
Ark68
A
J
  • Solved
multiple values
Replies
2
Views
210
JOEE1979
J
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
351
sspatriots
S
D
  • Solved
Search value in textbox1 in the listbox1 with array doesn't work
Replies
4
Views
167
david2005wang
D

Forum statistics

Threads
1,215,382
Messages
6,124,618
Members
449,175
Latest member
Anniewonder

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top