VBA - Userform command button that checks for duplicates

[sdemps]

Hello all,
I am making a userform that enters data into an excel sheet and assigns a unique part number to the new row of data when the command button is pressed.

I want to make the command button check for any duplicate rows before it adds the data to the excel sheet. However, the command must ignore the first column and last column when checking (The first column is the unique part number that is assigned, the last column is used in the creation of that part number). If it finds that the data is a duplicate, I will make a message box appear to inform the user and the data will not be added.

Here is my code for the command button. I know it is probably very inefficient and I apologize, I'm on day 3 using VBA but having a lot of fun learning it. Any help would be much appreciated!

Private Sub cb1_Click()
Dim emptyRow As Long
Dim N As Long
Sheets("Bearings").Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

If Me.cboSpecific = "Bearing" Then

    If Cells(emptyRow - 1, 1).Value = "Part_Number" Then
    Cells(emptyRow, 13).Value = 10000
    Else: Cells(emptyRow, 13).Value = Cells(emptyRow - 1, 13).Value + 1
    End If
    
Cells(emptyRow, 2).Value = Me.cbo3.Value 'BRG_Type
    
    If Me.cbo3 = "Angular Contact" Then
    Cells(emptyRow, 3).Value = Me.cbo4.Value 'BRG_Element
    Cells(emptyRow, 4).Value = Me.cbo5.Value 'BRG_Angle
    Cells(emptyRow, 5).Value = Me.cbo6.Value 'BRG_PL
    Cells(emptyRow, 6).Value = Me.cbo7.Value 'BRG_Series
    ElseIf Me.cbo3 = "Cylindrical Contact" Then
    Cells(emptyRow, 3).Value = Me.cbo4.Value 'BRG_Element
    Cells(emptyRow, 7).Value = Me.cbo5.Value 'BRG_Bore
    Cells(emptyRow, 8).Value = Me.cbo6.Value 'BRG_Roller
    End If
    
    If Me.OB2.Value = True Then
    Cells(emptyRow, 9).Value = Me.tb1.Value + "mm" 'BRG_ID in mm
    Cells(emptyRow, 10).Value = Me.tb2.Value + "mm" 'BRG_OD in mm
    Cells(emptyRow, 11).Value = Me.tb3.Value + "mm" 'BRG_Width in mm
    ElseIf Me.OB1.Value = True Then
    Cells(emptyRow, 9).Value = Me.tb1.Value + "in" 'BRG_ID in inches
    Cells(emptyRow, 10).Value = Me.tb2.Value + "in" 'BRG_OD in inches
    Cells(emptyRow, 11).Value = Me.tb3.Value + "in" 'BRG_Width in inches
    End If

Cells(emptyRow, 12).Value = Cells(emptyRow, 9) & " X " & Cells(emptyRow, 10) & " X " & Cells(emptyRow, 11) & " " & Cells(emptyRow, 2) & " BRG " & Cells(emptyRow, 6) & " " & Cells(emptyRow, 4) & " " & Cells(emptyRow, 7) & " " & Cells(emptyRow, 8) & " " & Cells(emptyRow, 3) & " " & Cells(emptyRow, 5)
Cells(emptyRow, 1).Value = "BRG-" & Cells(emptyRow, 13).Value
MsgBox ("Part Number: " & Cells(emptyRow, 1) & vbNewLine & "Description: " & Cells(emptyRow, 12) & vbNewLine & "Type: Purchased" & vbNewLine & "UOM Class: Counted Units" & vbNewLine & "Primary UOMs: PAIR" & vbNewLine & "Group: Spindle Repair" & vbNewLine & "Class: Hardware" & vbNewLine & "Material Analysis: Bearings" & vbNewLine & "Search: Bearing" & vbNewLine & "Use Part Rev: No")


End If

End Sub

Thanks!

[Dimitrov]

sdemps,

Would you be open to keeping such records in Access? If you are trying to assign unique part numbers, this may be a job for Access, since it sounds like you are in need of unique primary key for your part number. Access does this check automatically, and it does it well. You can either have Access assign a unique part number for each new item, or you can enter in the part number. Access will compare the part number that you enter against the existing part numbers in the table and will not let you enter the new record unless you key in a new part number that is not equal to any of the existing part numbers. This way, you can avoid having to write code that checks your new part number against existing part numbers. Just a thought, as I am not sure how your table (or tables) are built.

[sdemps]

sdemps,

Would you be open to keeping such records in Access? If you are trying to assign unique part numbers, this may be a job for Access, since it sounds like you are in need of unique primary key for your part number. Access does this check automatically, and it does it well. You can either have Access assign a unique part number for each new item, or you can enter in the part number. Access will compare the part number that you enter against the existing part numbers in the table and will not let you enter the new record unless you key in a new part number that is not equal to any of the existing part numbers. This way, you can avoid having to write code that checks your new part number against existing part numbers. Just a thought, as I am not sure how your table (or tables) are built.

Thanks Dimitrov,
Yes, I decided last Friday day to move the records to Access. However If anyone knows how to do this in excel I would still be interested in learning.

[Dimitrov]

Sounds good, and best of success with the project! I do not know all the details, but it sounds fun (as it sounds like you are building up a database from the ground up).

Not sure if you are hoping that someone will continue to add posts to this thread, but if not, considering marking this thread as [SOLVED]. To do this:

-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Thank you.