Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 34

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

  1. #21
    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

    Yes it is.

  2. #22
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,900
    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

    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


    Code:
    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                  '= data including headers
        lr = UBound(Arr)                                    '= how many rows including headers
       
    'concatenation of relevant values in userform
        For Each Ctrl In Array(ComboBox1, ComboBox2, TxtBox6, TxtBox7, ComboBox8, ComboBox9, TxtBox10, TxtBox11)
            V = Ctrl.Text
            If IsNumeric(V) Then V = CDbl(V)                'convert to type double if numeric
            textUF = textUF & "|" & V
        Next Ctrl
    Debug.Print "UF", textUF    'DELETE after testing
    'concatenation of relevant values in worksheet
        For r = 2 To lr
            textWS = ""
            For c = 1 To 11
                If c = 3 Then c = 6
                textWS = textWS & "|" & Arr(r, c)
            Next c
    Debug.Print "WS", textWS    'DELETE after testing
    '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
    'update values in worksheet and clear userform
            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
    Last edited by Yongle; Aug 17th, 2019 at 07:51 AM.

  3. #23
    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 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

  4. #24
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,900
    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

    Try this instead
    Code:
    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

    Code:
    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
    Code:
    Private Sub cmdTransfer_Click()
    call it from within another userform procedure with this line
           CmdClearForm_Click
    End Sub
    Last edited by Yongle; Aug 19th, 2019 at 12:24 AM.

  5. #25
    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

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

  6. #26
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,900
    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

    Thanks for the feedback

  7. #27
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,900
    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

    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)

  8. #28
    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

    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.

  9. #29
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,900
    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

    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

    Code:
    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)
    'create concatenation of relevant values in worksheet
        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
        Next r
    'add sheet
        Set sh = Sheets.Add(before:=ws)
        sh.Name = Format(Date, "YYMMDD ") & Round(Timer, 0)
    'check for duplicates and write to sheet
        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
    Last edited by Yongle; Aug 19th, 2019 at 07:09 AM.

  10. #30
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,900
    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

    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

    Code:
    Add another variable
    Dim Duplic As Boolean
    
    ...rest of code
    
                    If MsgBox("Do you want to clear userform values", vbYesNo) = vbYes Then ClearUserform
                      ComboBox1.SetFocus
                      Exit Sub
                   Else
                     Duplic = True
                     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
          
            If Duplic then 
            'write whichever values you want to the sheet containing your list of duplicates
                Sheets("DuplicatesList").Cells(Rows.count, 1).End(xlUp).Offset(1).Resize(, 2) = Array("Row "  & lr + 1, textWS )
            End If
            Duplic = False
    ... rest of code
    Last edited by Yongle; Aug 19th, 2019 at 07:48 AM.

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
  •