Page 4 of 4 FirstFirst ... 234
Results 31 to 34 of 34

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

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

    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.

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

    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  ' user said "NO" to updating the sheet
                    Call UpdateSheet(wsDupl)
                    Exit For
                End If
            End If
    4 Delete the original lines in cmdTransfer updating sheet ws
    Code:
    you are deleting these lines
            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)   'this replaces all those lines updating the sheet
          
            If Duplic then 
               Call UpdateSheet(wsDupl)            
            End If
            Duplic = False
    Last edited by Yongle; Aug 22nd, 2019 at 05:50 AM.

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

    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!

  4. #34
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,912
    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 your feedback

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
  •