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!

 

[Yongle]

And why did you change the 1 to a 2 here ?

For c = [COLOR=#ff0000]1[/COLOR] To rng.Columns.Count

 

[ggg111]

Hi Yongle!

I found out that the Error 2015 occurs because the formula on my sheet returns #VALUE ! I already know how to avoid the error however there's still a problem I'm encountering, whenever I put numbers in the form as a duplicate of existing one the sheet accepts it. The restriction of duplicates only works for letters not numbers how do I solve this?

Also how do I modify this code where if "Duplicate of row" shows I will have an option like "Enter Different Data" and then go back to the form instead of deleting a row.

SkipRowUpdate:

            If MsgBox("delete row " & lr & " ?", vbYesNo, "Duplicate of row " & r) = vbYes Then ws.Rows(lr).EntireRow.Delete
      
        End

Again, thank you so much for your help. IT'S A LOT THANK YOU SO MUCH!

 

[Yongle]

No problem

Let's go back to the beginning and agree where we are heading

Suggested approach (simpler than what you are trying to do - simple is good!)

1. Update the row
2. Compare worksheet values
3. The comparison should exclude formulas
4. Delete unwanted duplicated row

Assuming you agree ...
- what is the column range for your table ?
- which of those columns contain formulas?
- is the user permitted to confirm a duplicate row or should it always be deleted ?

 

[ggg111]

I agree with what your implying ofcourse simple is always good, however, I have been trying to make this work and I'm actually almost done and my only problem left is that values like date for example is 16/8/19, values with decimal like 0.50 and time values like 7:00 AM is not being recognized as a duplicate.

Here's the transfer button's code that i come up with, with your help. (I also learned about combobox so I used it as a replacement for some textbox)

Edit: My sheet contains 12 columns and the 12th column is the one that has formula (product of column 10 * column 11)

Private Sub cmdTranfer_Click()
    Dim ws As Worksheet, rng As Range, arr As Variant
    Dim lr As Long, r As Long, c As Long
    Dim textWS As String, textUF As String
    Set ws = Sheets("Sheet1")
    Set rng = ws.Range("A1").CurrentRegion
    arr = rng
    lr = UBound(arr)

    Dim ctrl As Variant
    
    For Each ctrl In Array(ComboBox1, ComboBox2, ComboBox3, TxtBox4, TxtBox5, TxtBox6, TxtBox7, ComboBox8, ComboBox9, TxtBox10, TxtBox11)
        textUF = textUF & "|" & ctrl.Text
    Next ctrl
    


    For r = 1 To lr
        textWS = ""
        For c = 1 To rng.Columns.Count
            textWS = textWS & "|" & arr(r, c)
        
        If textWS = textUF Then GoTo SkipRowUpdate
        Next
    Next r

            LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

                ws.Range("A" & LastRow).Value = ComboBox1.Text
                ws.Range("B" & LastRow).Value = ComboBox2.Text
                ws.Range("C" & LastRow).Value = ComboBox3.Text
                ws.Range("D" & LastRow).Value = TxtBox4.Text
                ws.Range("E" & LastRow).Value = TxtBox5.Text
                ws.Range("F" & LastRow).Value = TxtBox6.Text
                ws.Range("G" & LastRow).Value = TxtBox7.Text
                ws.Range("H" & LastRow).Value = ComboBox8.Text
                ws.Range("I" & LastRow).Value = ComboBox9.Text
                ws.Range("J" & LastRow).Value = TxtBox10.Text
                ws.Range("K" & LastRow).Value = TxtBox11.Text

Exit Sub


SkipRowUpdate:

            If MsgBox("Enter This Data?", vbYesNo, "DUPLICATE OF ROW " & r) = vbYes Then
            
            LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

                ws.Range("A" & LastRow).Value = ComboBox1.Text
                ws.Range("B" & LastRow).Value = ComboBox2.Text
                ws.Range("C" & LastRow).Value = ComboBox3.Text
                ws.Range("D" & LastRow).Value = TxtBox4.Text
                ws.Range("E" & LastRow).Value = TxtBox5.Text
                ws.Range("F" & LastRow).Value = TxtBox6.Text
                ws.Range("G" & LastRow).Value = TxtBox7.Text
                ws.Range("H" & LastRow).Value = ComboBox8.Text
                ws.Range("I" & LastRow).Value = ComboBox9.Text
                ws.Range("J" & LastRow).Value = TxtBox10.Text
                ws.Range("K" & LastRow).Value = TxtBox11.Text
      
        End If


End Sub

 

[Yongle]

Thanks
Currently busy for a few hours - will post updated code later today

 

[Yongle]

I have been trying to make this work and I'm actually almost done

Maybe ..

and my only problem left is that values like date for example is 16/8/19, values with decimal like 0.50 and time values like 7:00 AM is not being recognized as a duplicate.

This may help you do what is required to make your matches work

Textboxes and comboboxes contain text which is interpreted by Excel
If it looks like standard text or number then there are no matching issues
If it looks like a date then Excel converts it into an integer and displays it as a date
If it looks like a time then Excel converts it into a decimal and displays it as time

Formatting is NOT the issue - one value is a STRING and the other value is NUMERIC

16 August 2019
As a user you see the same in both in the worksheet and the userform (even when formatted differently - your brain says 16/08/19 = 16 August 2019 ) = MATCH
But VBA sees (text) "16 August 2019" in textbox and (integer) 43693 in Excel (43693 = the serial value for 16 August 2019) = NO MATCH

09:00am
As a user you see 9 o'clock (morning) in both the worksheet and the userform (- even if formatted differently - your brain says 9am = 09:00:00 ) = MATCH
But VBA sees (text) "09:00am" in textbox and (decimal) 0.375 in Excel (0.375 = the timevalue for 9am) = NO MATCH

To match the value in the userform with what is in the worksheet, then the value in the textbox must be converted to the same type of value as contained in the worksheet

Dates
These message boxes all return True

Dim x As Boolean
x = (CDate("16 August 2019") = 43693)
MsgBox x
x = (CDate("August 16 2019") = 43693)
MsgBox x
x = (CDate("16/08/19") = 43693)
MsgBox x

'use this converted textbox date in the comparison

CDate(TxtBox99.Text)

See if you can get the dates to match up first before attacking times and decimals

Times
How are times being entered - are they typed in looking like this 09:30 ?

Decimals
I am guessing that this is the likely issue:
Text "22.50" entered in textbox is interpreted by Excel as the number 22.5
The userform concatenation includes 22.50 , whereeas the worksheet is concatenation is 22.5
You could try converting the textbox text to variable type double in the concatenation string

CDbl(TxtBox88.Text)

I am happy to help you puzzle your way through this, but would like to understand why you are reluctant to add the row to the worksheet ?
- values in the worksheet do not need converting to match up

 

[ggg111]

Hi Yongle!

I'm sorry for a lot of questions I'm just really new to VBA and excel, it's my very first time creating a userform.

Can I post a sample workbook so you can check what I've been doing with this. I just realized I've been doing this wrong, I'm sorry I haven't explained what's the purpose of this, want I'm currently working is about garment's manufacturing and the data in the sheet is Employee's finished job reports recorded by date and time and how much did they make, so each row represents a unique job report. There are different Bundles (one of the 12 columns) and every Bundle goes to different Operations (one of the 12 columns) and they must not be put to the same Operation twice (continues flow) meaning if a Bundle is put as Edging 1 the next time it will be recorded it must be put as Edging 2 and so on.

For example,

ITEM CODE | JOB | NAME | DATE | TIME | BUNDLE | COLOR | OPERATION | SIZE | QTY. | PRICE | AMOUNT

BOT-2476 | SEWING | JOHN DOE | 17/8/19 | 10:00 AM | 1042 | WHITE | EDGING 1 | S | 50 | 0.50 | 25.00

The BUNDLE number "1042" has it's constant value along with it the ITEM CODE "BOT-2476", JOB "SEWING", COLOR "WHITE", SIZE "S" and QUANTITY "50" if it was put in the OPERATION as "EDGING 1" then it must only occur once as "EDGING 1", SO if some other employee turns out to have a report similar to what "JOHN DOE" has like the same "BOT-2476", "SEWING", "1042", "WHITE", "EDGING 1", "S" and "50" this is the time i will be notified that the entry is repeated or "ANOTHER EMPLOYEE NAME" reported a finished JOB that is already done by "JOHN DOE". To be clear, "BOT-2476", "SEWING", "WHITE", "S" and "50" can be repeated as long as it's with a different Operation like "EDGING 2" and so on.

Of course it's important for me to know if a whole row is repeated but i missed the main purpose of what I'm doing and it's to be notified if an employee have done a job that is already done by someone else.

I hope I was being clear I'm sorry not that good at english.
And I'm sorry for giving a lot of problems as you can see I'm literally new to excel and I can't think of any possible way to execute this but just restricting duplicates.
Again, thank you so much for a lot of help! I learned things now andI'm actually enjoying it.

 

[Yongle]

I hope I was being clear I'm sorry not that good at english.

Your English is very good and is much better than many people who only speak English !


.

Of course it's important for me to know if a whole row is repeated but i missed the main purpose of what I'm doing and it's to be notified if an employee have done a job that is already done by someone else

The WHOLE row is being compared - but I do not think that is what you require ...
In (post#17) example John Doe did the work.
Q1 If the same values are also entered for Jane Doe ... is that a duplicated row ?


.

I just realized I've been doing this wrong

Q2 Do you want this instead
- give user option to delete the values if an input error
- identify all duplicated rows (to provide warning if an employee has done a job that is already done by someone else)

 

[ggg111]

Your English is very good and is much better than many people who only speak English !

Thanks for that!

Q1 If the same values are also entered for Jane Doe ... is that a duplicated row ?

Yes exactly! Like this,

ITEM CODE | JOB | NAME | DATE | TIME | BUNDLE | COLOR | OPERATION | SIZE | QTY. | PRICE | AMOUNT

BOT-2476 | SEWING | JANE DOE | 17/8/19 | 10:00 AM | 1042 | WHITE | EDGING 1 | S | 50 | 0.50 | 25.00

This is the time I must be warned that it is a duplicate, the date and time can be different tho but it won't matter and must still be considered a duplicate.

Q2 Do you want this instead
- give user option to delete the values if an input error

Yes, so I can also have a record of input errors

- identify all duplicated rows (to provide warning if an employee has done a job that is already done by someone else)

Yes! This will help me thank you so much!

 

[Yongle]

Is this the correct definition of "duplicate row" ?

RED values match current userform values

ITEM CODE | JOB | NAME | DATE | TIME | BUNDLE | COLOR | OPERATION | SIZE | QTY. | PRICE | AMOUNT