VBA - Userform command button that checks for duplicates

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!

Please Login or Register  to view this content.

Thanks!

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.

Originally Posted by 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.

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.

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.