Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

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

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 CODE JOB NAME DATE TIME BUNDLE# COLOR OPERATION SIZE QUANTITY PRICE
    COLORED T/S SEWING GASPAR NOE 6/8/19 7:00 AM 035 WHITE EDGING 1 XL 50 0.40
    COLORED T/S SEWING GASPAR NOE 6/8/19 7:00 AM 035 WHITE EDGING 2 XL 50 0.50


    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 CODE JOB NAME DATE TIME BUNDLE# COLOR OPERATION SIZE QUANTITY PRICE
    COLORED T/S SEWING GASPAR NOE 6/8/19 7:00 AM 035 WHITE EDGING 1 XL 50 0.40
    COLORED T/S SEWING GASPAR NOE 6/8/19 7:00 AM 035 WHITE EDGING 1 XL 50 0.40



    Thank you so much in advance!

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,871
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

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

    I assume that you want to test the LAST entered row to see if there is another row further up that is totally identical

    with this data ...

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    ITEM CODE JOB NAME DATE TIME BUNDLE# COLOR OPERATION SIZE QUANTITY PRICE
    2
    COLORED T/S SEWING GASPAR NOE
    06/08/2019
    7:00 AM
    35
    WHITE EDGING 1 XL
    50
    0.4
    3
    COLORED T/S SEWING GASPAR NOE
    06/08/2019
    7:00 AM
    35
    WHITE EDGING 2 XL
    50
    0.5
    4
    COLORED T/S SEWING GASPAR NOE
    06/08/2019
    7:00 AM
    35
    WHITE EDGING 3 XL
    50
    0.5
    5
    COLORED T/S SEWING GASPAR NOE
    06/08/2019
    7:00 AM
    35
    WHITE EDGING 4 XL
    50
    0.5
    6
    COLORED T/S SEWING GASPAR NOE
    06/08/2019
    7:00 AM
    35
    WHITE EDGING 2 XL
    50
    0.5
    Sheet: Data

    .. VBA below detects that the last row is a duplicate of row 3, and offers user opportunity to delete that row

    Code:
    Sub ggg()
        Dim ws As Worksheet, rng As Range, arr As Variant, combo As Variant, text As String
        Dim lr As Long, r As Long, c As Long
        Set ws = Sheets("Data")
        Set rng = ws.Range("A1").CurrentRegion
        arr = rng
        lr = UBound(arr)
        ReDim combo(1 To lr)
        
        For r = 1 To lr
            text = ""
            For c = 1 To rng.Columns.Count
                text = text & "|" & arr(r, c)
            Next
            combo(r) = text
        Next r
        
        For r = 1 To lr - 1
            If combo(lr) = combo(r) Then
                If MsgBox("delete row " & lr & " ?", vbYesNo, "Duplicate of row " & r) = vbYes Then ws.Rows(lr).EntireRow.Delete
                Exit Sub
            End If
        Next
    End Sub
    Notes
    1. Values are placed in array
    2. Values in array are concatenated to create a single string for each row (as in list below)
    row1 |ITEM CODE|JOB|NAME|DATE|TIME|BUNDLE#|COLOR|OPERATION|SIZE|QUANTITY|PRICE
    row2 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 1|XL|50|0.4
    row3 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 2|XL|50|0.5
    row4 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 3|XL|50|0.5
    row5 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 4|XL|50|0.5
    row6 |COLORED T/S|SEWING|GASPAR NOE|06/08/2019|0.291666666666667|35|WHITE|EDGING 2|XL|50|0.5
    3. The LASTrow string is compared against every other row string until a match is found
    Last edited by Yongle; Aug 10th, 2019 at 09:42 AM.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Yongle!

    Thank you so much for your answer! I appreciate it a lot but how do I use this code on a form? I wanted to use a form and restrict duplicate entries, here's how it looks

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Yongle!

    I already tried it using a form thank you so much. Also, I didn't want to delete the existing duplicate and then be replaced by my new entry, I just wanted to know if my new entry is a duplicate and then I won't let it be put in the data sheet. How do I execute this?

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,871
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

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

    Instead of comparing after putting the values in the worksheet
    - create a concatenation of current userform control values
    - the sequence of the concatenation needs to be the same as the sequence of the columns in the worksheet
    - compare to the concatenation of each row

    The code would look something like this:
    Code:
    Private Sub CommandButtonXXX_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("Data")
        Set rng = ws.Range("A1").CurrentRegion
        arr = rng
        lr = UBound(arr)
    
    'concatenate current userform values ( PSEUDO CODE )
    
        For each (relevant) Control in your userform controls
           textUF = textUF & "|" & ThisControl.Value
        Next Control
    
    'concatenate worksheet values in each row and compare
        For r = 1 To lr
            textWS = ""
            For c = 1 To rng.Columns.Count
                textWS = textWS & "|" & arr(r, c)
            Next
            If textWs = textUF then GoTo SkipRowUpdate
        Next r
    
    'update row values 
       'code to write userform values to worksheet goes here
    
    Exit Sub
    
    SkipRowUpdate:
           MsgBox "Duplicate of row " & r
            'whatever else needs to happen
    End Sub
    Last edited by Yongle; Aug 13th, 2019 at 03:35 AM.

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Yongle! Sorry for replying late! Thanks for the code but I'm having a hard time understanding everything may i know what's wrong with the code i made.

    Code:
    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("A2").CurrentRegion
        arr = rng
        lr = UBound(arr)
    
    'concatenate current userform values ( PSEUDO CODE )
    
    For lr = 2 To lr
    
    textUF = textUF & "|" & TxtBox1.Text
    textUF = textUF & "|" & TxtBox2.Text
    textUF = textUF & "|" & TxtBox3.Text
    textUF = textUF & "|" & TxtBox4.Text
    textUF = textUF & "|" & TxtBox5.Text
    textUF = textUF & "|" & TxtBox6.Text
    textUF = textUF & "|" & TxtBox7.Text
    textUF = textUF & "|" & TxtBox8.Text
    textUF = textUF & "|" & TxtBox9.Text
    textUF = textUF & "|" & TxtBox10.Text
    textUF = textUF & "|" & TxtBox11.Text
    
        Next lr
        
    
    'concatenate worksheet values in each row and compare
        For r = 2 To lr
            textWS = ""
            For c = 1 To rng.Columns.Count
                textWS = textWS & "|" & arr(r, c)
            Next
            If textWS = textUF Then GoTo SkipRowUpdate
        Next r
    
    'update row values
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells(lRow, 1).Value = TxtBox1.Text
    Cells(lRow, 2).Value = TxtBox2.Text
    Cells(lRow, 3).Value = TxtBox3.Text
    Cells(lRow, 4).Value = TxtBox4.Text
    Cells(lRow, 5).Value = TxtBox5.Text
    Cells(lRow, 6).Value = TxtBox6.Text
    Cells(lRow, 7).Value = TxtBox7.Text
    Cells(lRow, 8).Value = TxtBox8.Text
    Cells(lRow, 9).Value = TxtBox9.Text
    Cells(lRow, 10).Value = TxtBox10.Text
    Cells(lRow, 11).Value = TxtBox11.Text
    
    Exit Sub
    
    SkipRowUpdate:
           MsgBox "Duplicate of row " & r
            'whatever else needs to happen
    End Sub

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,871
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

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

    Instead of this
    Code:
    For lr = 2 To lr
         textUF = textUF & "|" & TxtBox1.Text
         textUF = textUF & "|" & TxtBox2.Text
        .....
         textUF = textUF & "|" & TxtBox11.Text
    Next lr
    use this method
    Code:
    'declare another variable  (it must be declared as variant in this case)
        Dim ctrl As Variant
        
    'loop through like this
        For Each ctrl In Array(TxtBox1, TxtBox2, TxtBox3, TxtBox4, TxtBox5, TxtBox6, TxtBox7, TxtBox8, TxtBox9, TxtBox10, TxtBox11)
            textUF = textUF & "|" & ctrl.Text
        Next ctrl

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    the part " textWS = textWS & "|" & arr(r, c) " says "Error 2015" what would be the problem here?

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

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Also how do I code the form where new entries that is not a duplicate will be put in the last new row of the data table?

  10. #10
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,871
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

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

    the part " textWS = textWS & "|" & arr(r, c) " says "Error 2015" what would be the problem here?
    Why did you change A1 to A2 in this line?

    Code:
    Set rng = ws.Range("A1").CurrentRegion

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •