Restrict Duplication Of A Whole Row When Entering New Data Using VBA Form

[ggg111]

Good Day!

I've been looking for a solution for this problem and I can't find any since I'm new to VBA. So I have a sheet that contains 12 columns and i wanted to use VBA Form when entering new data every row is supposed to be different from each other but it's columns can have repeated values, what i'm trying to do is restrict entry of new datas that is already in the sheet but as a whole row. I already tried the data validation but it only works in columns

here's a sample table for better understanding,
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]JOB[/TD]
[TD]NAME[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[TD]BUNDLE#[/TD]
[TD]COLOR[/TD]
[TD]OPERATION[/TD]
[TD]SIZE[/TD]
[TD]QUANTITY[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]COLORED T/S[/TD]
[TD]SEWING[/TD]
[TD]GASPAR NOE[/TD]
[TD]6/8/19[/TD]
[TD]7:00 AM[/TD]
[TD]035[/TD]
[TD]WHITE[/TD]
[TD]EDGING 1[/TD]
[TD]XL[/TD]
[TD]50[/TD]
[TD]0.40[/TD]
[/TR]
[TR]
[TD]COLORED T/S[/TD]
[TD]SEWING[/TD]
[TD]GASPAR NOE[/TD]
[TD]6/8/19[/TD]
[TD]7:00 AM[/TD]
[TD]035[/TD]
[TD]WHITE[/TD]
[TD]EDGING 2[/TD]
[TD]XL[/TD]
[TD]50[/TD]
[TD]0.50[/TD]
[/TR]
</tbody>[/TABLE]


as you can see, almost everything in the two rows is the same except the EDGING 1 and EDGING 2 and it's PRICE. I wanted my form to not accept the entry (like display a warning box or something) if what i'm trying to enter in the second row is identically the same as in the first row

like this,
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]JOB[/TD]
[TD]NAME[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[TD]BUNDLE#[/TD]
[TD]COLOR[/TD]
[TD]OPERATION[/TD]
[TD]SIZE[/TD]
[TD]QUANTITY[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]COLORED T/S[/TD]
[TD]SEWING[/TD]
[TD]GASPAR NOE[/TD]
[TD]6/8/19[/TD]
[TD]7:00 AM[/TD]
[TD]035[/TD]
[TD]WHITE[/TD]
[TD]EDGING 1[/TD]
[TD]XL[/TD]
[TD]50[/TD]
[TD]0.40[/TD]
[/TR]
[TR]
[TD]COLORED T/S[/TD]
[TD]SEWING[/TD]
[TD]GASPAR NOE[/TD]
[TD]6/8/19[/TD]
[TD]7:00 AM[/TD]
[TD]035[/TD]
[TD]WHITE[/TD]
[TD]EDGING 1[/TD]
[TD]XL[/TD]
[TD]50[/TD]
[TD]0.40[/TD]
[/TR]
</tbody>[/TABLE]



Thank you so much in advance!

 

[ggg111]

Hi Yongle!

I just tried using the code you sent thanks for it, I used the second one, so I see that it puts the duplicates in the designated sheet only of I enter the duplicate data in my data sheet also it only puts this,

[TABLE="width: 543"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD]Row 5655
[/TD]
[TD="colspan: 5"]|COLORED T/S|SEWING|1513|YELLOW SLUB|EDGING 4|L|50|0.6
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 543"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD="colspan: 5"][/TD]
[/TR]
</tbody>[/TABLE]
how do I put the exact whole row in the "Duplicate List" but without entering it to my data sheet, like, if the form says "Duplicate of row 5655... Enter this data?" and then if i click "No" it will automatically put all the values in the textboxes in the "Duplicate List" sheet.

 

[Yongle]

To avoid repeating the code to update different sheets, it would be better to create a procedure to do that and use a variable to point it at the correct sheet

1 Add this sub in userform module

Private Sub UpdateSheet(aSheet As Worksheet)
    Dim rng As Range
    Set rng = aSheet.Range("A" & Rows.count).End(xlUp).Offset(1).Resize(, 11)
    rng(1, 1) = ComboBox1.Text
    rng(1, 2) = ComboBox2.Text
    rng(1, 3) = ComboBox3.Text
    rng(1, 4) = TxtBox4.Text
    rng(1, 5) = TxtBox5.Text
    rng(1, 6) = TxtBox6.Text
    rng(1, 7) = TxtBox7.Text
    rng(1, 8) = ComboBox8.Text
    rng(1, 9) = ComboBox9.Text
    rng(1, 10) = TxtBox10.Text
    rng(1, 11) = TxtBox11.Text
End Sub

2 Add sheet variable in cmdTransfer

  Dim wsDupl As WorkSheet
  Set wsDupl = Sheets("DuplicatesList")

3 Amend cmdTransfer so that it now updates DuplicatesList when user does not add duplicated row
(I think this is the code that you are currently using) Insert one line

        If textWS = textUF Then
            If MsgBox("Enter This Data?", vbYesNo, "DUPLICATE OF ROW " & r) <> vbYes Then
                If MsgBox("Do you want to clear userform values", vbYesNo) = vbYes Then ClearUserform
              ComboBox1.SetFocus
              Exit Sub
            Else  [COLOR=#006400][I]' user said "NO" to updating the sheet[/I][/COLOR]
                [COLOR=#ff0000]Call UpdateSheet(wsDupl)[/COLOR]
                Exit For
            End If
        End If

4 Delete the original lines in cmdTransfer updating sheet ws

[I][COLOR=#006400]you are deleting these lines[/COLOR][/I]
        Set rng = ws.Range("A" & lr + 1).Resize(, 11)
        rng(1, 1) = ComboBox1.Text
        rng(1, 2) = ComboBox2.Text
        rng(1, 3) = ComboBox3.Text
        rng(1, 4) = TxtBox4.Text
        rng(1, 5) = TxtBox5.Text
        rng(1, 6) = TxtBox6.Text
        rng(1, 7) = TxtBox7.Text
        rng(1, 8) = ComboBox8.Text
        rng(1, 9) = ComboBox9.Text
        rng(1, 10) = TxtBox10.Text
        rng(1, 11) = TxtBox11.Text

5 Amend cmdTransfer to update both worksheets

        Call UpdateSheet(ws)   '[I][COLOR=#006400]this replaces all those lines updating the sheet[/COLOR][/I]
      
        If Duplic then 
           Call UpdateSheet(wsDupl)            
        End If
        Duplic = False

 

[ggg111]

Thanks Yongle!

It's working now, I also put the line
"Call UpdateSheet(wsDupl)"

after

"If MsgBox("Enter This Data?", vbYesNo, "DUPLICATE OF ROW " & r) <> vbYes Then"


so that upon clicking No to the question "Enter this data?" it will be put to the duplicates sheet

Thanks again you have been a great help for me I think the works i made with the form is okay for now thank you so much!

 

[Yongle]

Thanks for your feedback