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]

Yes it is.

 

[Yongle]

Does this achieve the correct matches ?
- it works for me but you may have some other values that Excel is treating differently
- we will consider the "row duplicate warning" AFTER you have made the matching work

Some of the changes made

1 Sub added to clear userform values (to avoid repeating the code)
2 Code updating worksheet values simplified (your code was good , my code is a different way to do exactly the same thing)

3 Note how numeric userform values are converted to match up with how Excel will treat them
If IsNumeric(V) Then V = CDbl(V)

4 Code If c = 3 Then c = 6 makes the code "jump" to column 6 (avoids concatenating Name, Date and Time)

Debug.Print is useful when you are trying to see what is wrong (can delete when testing is finished)
This is what I see with only ONE row in the worksheet and the SAME data in the userform (you will get many lines for WS)
UF |BOT-2476|SEWING|1042|WHITE|EDGING 1|S|50|0.5
WS |BOT-2476|SEWING|1042|WHITE|EDGING 1|S|50|0.5

See the result of Debug.Print in Immediate Window - {CTRL} G makes immediate window visible when in VBA

Private Sub cmdTranfer_Click()
    Dim ws As Worksheet, rng As Range
    Dim Arr As Variant, Ctrl As Variant, V As Variant
    Dim lr As Long, r As Long, c As Long
    Dim textWS As String, textUF As String
    
    Set ws = Sheets("Sheet1")
    Arr = ws.Range("A1").CurrentRegion                  [I][COLOR=#006400]'= data including headers[/COLOR][/I]
    lr = UBound(Arr)                                    '[I][COLOR=#006400]= how many rows including headers[/COLOR][/I]
   
[COLOR=#006400][I]'concatenation of relevant values in userform[/I][/COLOR]
    For Each Ctrl In Array(ComboBox1, ComboBox2, TxtBox6, TxtBox7, ComboBox8, ComboBox9, TxtBox10, TxtBox11)
        V = Ctrl.Text
        If IsNumeric(V) Then V = CDbl(V)               [I][COLOR=#006400] 'convert to type double if numeric[/COLOR][/I]
        textUF = textUF & "|" & V
    Next Ctrl
[COLOR=#ff0000]Debug.Print "UF", textUF    [/COLOR][I][COLOR=#006400]'DELETE after testing[/COLOR][/I]
[I][COLOR=#006400]'concatenation of relevant values in worksheet[/COLOR][/I]
    For r = 2 To lr
        textWS = ""
        For c = 1 To 11
            If c = 3 Then c = 6
            textWS = textWS & "|" & Arr(r, c)
        Next c
[COLOR=#ff0000]Debug.Print "WS", textWS    [/COLOR][I][COLOR=#006400]'DELETE after testing[/COLOR][/I]
'is there a duplicate ?
        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
                Exit For
            End If
        End If
    Next r
[I][COLOR=#006400]'update values in worksheet and clear userform[/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
        ClearUserform
End Sub

Private Sub ClearUserform()
    Dim Ctrl As Variant
    For Each Ctrl In Array(ComboBox1, ComboBox2, ComboBox3, TxtBox4, TxtBox5, TxtBox6, TxtBox7, ComboBox8, ComboBox9, TxtBox10, TxtBox11)
        Ctrl.Text = ""
    Next Ctrl
End Sub

 

[ggg111]

Hi Yongle!

Sorry for late reply, was away from my computer. Thank you for the code! Just a question, is the sub you added for clearing userform must have a button or can just be put the Transfer Button Code? Also I have a button in my form to clear the userform after entering data, should i replace it with this code? I tried just putting the codes as how you made it but vba finds error saying "Run-time error 380 Could not set the Text property. Invalid property value." and it pertains to the code Ctrl.Text = "" in clear userform sub

 

[Yongle]

Try this instead

Ctrl.Value = ""

You can do whatever you want with the ClearUserform code
- either place the code as part of the transfer code, or (as I have done) call it from the transfer code, or (if you prefer) have it as a button, or any combination of the three

example with code behind command button named CmdClearForm

Private Sub CmdClearForm_Click()
    Dim Ctrl As Variant
    For Each Ctrl In Array(ComboBox1, ComboBox2, ComboBox3, TxtBox4, TxtBox5, TxtBox6, TxtBox7, ComboBox8, ComboBox9, TxtBox10, TxtBox11)
        Ctrl.Value = ""
    Next Ctrl
End Sub

Private Sub cmdTransfer_Click()
[COLOR=#006400][I]call it from within another userform procedure with this line[/I][/COLOR]
       CmdClearForm_Click
End Sub

 

[ggg111]

Yes! Thank you so much! It works perfectly fine now!

 

[Yongle]

Thanks for the feedback

 

[Yongle]

1. You said you wanted to be able to identify duplicates row added by the user.

How do you want that achieved ?
- do you want a list of duplicate rows ?
- do you want duplicate rows highlighted in some way ?
- or something else ?

2. Have you considered adding the values in the 12th column as part of the userform code ?
- I presume that you are dragging down the formula manually at present
- the code could be used to insert the formula (or else the calculated value)

 

[ggg111]

How do you want that achieved ?
- do you want a list of duplicate rows ?

I'm not really sure as I don't know a lot about VBA but having another sheet in my workbook that will show the duplicated datas would be a great help because right now the only purpose of my form is to not accept the duplicated entries

2. Have you considered adding the values in the 12th column as part of the userform code ?

I don't know, maybe, but the 12th column already has formulas like if i enter a new row in the table using the form it automatically creates a cell in 12th column that has formula to multiply column 10 and 11 but thats just what i know maybe there are better ways.

 

[Yongle]

having another sheet in my workbook that will show the duplicated datas would be a great help

Try this

Note
- this is not using the userform in any way and should be placed in a standard module
- you could call it from a button or from a userform if preferred

This is an additional procedure which creates a new sheet listing all duplicate rows found
- you may prefer to place the values in an existing sheet
- amend to suit how you need it to work

Sub DuplicatesInSheet()
    Dim ws As Worksheet, sh As Worksheet
    Dim Arr As Variant, Arr2 As Variant
    Dim lr As Long, r As Long, c As Long, x As Long, HowMany As Long
    Dim textWS As String
    
    Set ws = Sheets("Sheet1")
    Arr = ws.Range("A1").CurrentRegion                  '= data including headers
    lr = UBound(Arr)                                    '= how many rows including headers
    ReDim Arr2(0 To lr)
[COLOR=#006400][I]'create concatenation of relevant values in worksheet[/I][/COLOR]
    For r = 1 To lr
        textWS = ""
        For c = 1 To 11
            If c = 3 Then c = 6
            textWS = textWS & "|" & Arr(r, c)
        Next c
        Arr2(r) = textWS
[I][COLOR=#006400]    Next r
'add sheet[/COLOR][/I]
    Set sh = Sheets.Add(before:=ws)
    sh.Name = Format(Date, "YYMMDD ") & Round(Timer, 0)
[I][COLOR=#006400]'check for duplicates and write to sheet[/COLOR][/I]
    For r = 2 To lr
        For x = 2 To lr
            If Arr2(x) = Arr2(r) Then
                HowMany = HowMany + 1
            End If
        Next x
        If HowMany > 1 Then sh.Cells(Rows.count, 1).End(xlUp).Offset(1).Resize(, 3) = Array("ROW " & r, HowMany, Arr2(r))
        HowMany = 0
    Next r
    If sh.Cells(2, 1) = "" Then sh.Cells(2, 1) = "None"
End Sub

I don't know, maybe, but the 12th column already has formulas like if i enter a new row in the table using the form it automatically creates a cell in 12th column that has formula to multiply column 10 and 11 but thats just what i know maybe there are better ways

- that is a perfectly good method
- ignore my earlier suggestion

 

[Yongle]

If you want the duplication recorded when the user is confirming the duplicate row then amend procedure cmdTransfer

Try something like this
- create a new sheet , amend name in code to match the name of the sheet
- use a booolean variable and set it to true if a duplicated value is being written to the sheet
- and add a line to write required values to sheet DuplicatesList
- in example below the row number and the concatenation are written to the sheet
- set variable to false

[COLOR=#006400][I]Add another variable[/I][/COLOR]
[COLOR=#ff0000]Dim Duplic As Boolean[/COLOR]

...rest of code

                If MsgBox("Do you want to clear userform values", vbYesNo) = vbYes Then ClearUserform
                  ComboBox1.SetFocus
                  Exit Sub
               Else
                 [COLOR=#ff0000]Duplic = True[/COLOR]
                 Exit For
              End If

... rest of code
'update values in worksheet and clear userform
        Set rng = ws.Range("A" & lr + 1).Resize(, 11)
        rng(1, 1) = ComboBox1.Text
..... etc
        rng(1, 11) = TxtBox11.Text
      
        [COLOR=#ff0000]If Duplic then [/COLOR]
   [I][COLOR=#006400]     'write whichever values you want to the sheet containing your list of duplicates[/COLOR][/I]
            [COLOR=#ff0000]Sheets("[/COLOR][COLOR=#000000][B]DuplicatesList[/B][/COLOR][COLOR=#ff0000]").Cells(Rows.count, 1).End(xlUp).Offset(1).Resize(, 2) = Array("Row "  & lr + 1, textWS )[/COLOR]
        [COLOR=#ff0000]End If[/COLOR]
        [COLOR=#ff0000]Duplic = False[/COLOR]
... rest of code