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

ggg111

New Member
Joined
Aug 10, 2019
Messages
16
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,
ITEM CODEJOBNAMEDATETIMEBUNDLE#COLOROPERATIONSIZEQUANTITYPRICE
COLORED T/SSEWINGGASPAR NOE6/8/197:00 AM035WHITEEDGING 1XL500.40
COLORED T/SSEWINGGASPAR NOE6/8/197:00 AM035WHITEEDGING 2XL500.50

<tbody>
</tbody>


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,
ITEM CODEJOBNAMEDATETIMEBUNDLE#COLOROPERATIONSIZEQUANTITYPRICE
COLORED T/SSEWINGGASPAR NOE6/8/197:00 AM035WHITEEDGING 1XL500.40
COLORED T/SSEWINGGASPAR NOE6/8/197:00 AM035WHITEEDGING 1XL500.40

<tbody>
</tbody>



Thank you so much in advance!
 
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,

Row 5655
|COLORED T/S|SEWING|1513|YELLOW SLUB|EDGING 4|L|50|0.6

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>


<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>
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.
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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

Code:
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
Code:
  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
Code:
        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
Code:
[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
Code:
        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
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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