Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

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

  1. #11
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,893
    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

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

    Code:
    For c = 1 To rng.Columns.Count

  2. #12
    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 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.
    Code:
    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!

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

    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 ?
    Last edited by Yongle; Aug 16th, 2019 at 03:16 AM.

  4. #14
    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

    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)

    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("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
    Last edited by ggg111; Aug 16th, 2019 at 04:35 AM.

  5. #15
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,893
    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
    Currently busy for a few hours - will post updated code later today

  6. #16
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,893
    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 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
    Code:
    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
    Code:
    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
    Code:
    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

  7. #17
    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'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.

  8. #18
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,893
    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 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)
    Last edited by Yongle; Aug 17th, 2019 at 02:16 AM.

  9. #19
    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

    Quote Originally Posted by Yongle View Post
    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!

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

    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

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
  •