Help preventing userform duplicates

**nohero** · 06-03-2011, 09:32 AM

The first field in my userform is to assign a serial number to a product. What can I do to prevent someone from using that same serial number to input a new product? I'd like some kind of error message stating that the serial number is already in use.

**Charles** · 06-03-2011, 09:48 AM

HI,

You can probably use a "Match" function, but here's a bit of code that I used to validate if nr. exist. This should give you some ideal.

Please Login or Register  to view this content.

**nohero** · 06-03-2011, 11:15 AM

Trying to get this going, I'm a newbie and still don't have a lot of experience so I'm unsure of this one line:

With Lws.Range("D6:D" & Llrow)

What am I customizing to my workbook in this line? Does Lws represent the worksheet that the field's data is going to and does the information in the brackets point to column that the previous entries are being stored in?

**Leith Ross** · 06-03-2011, 11:35 AM

Hello nohero,

I am not sure about your meaning of "userform". Are you referring to a worksheet or a VBA UserForm? If people are uncertain of what you mean then you are likely not to get a answer you expect.

**Charles** · 06-03-2011, 11:52 AM

HI,

The code:

Please Login or Register  to view this content.

The code looks in column D to see it item exsit if it does it will display the "MsgBox".

I hope this helps

**royUK** · 06-03-2011, 11:55 AM

I wouldn't allow users to input a serial number, auto-generate it in the UserForm. I've posted code to do this but post back if that's something that would work

**nohero** · 06-03-2011, 03:41 PM

Actually I can't just auto-assign serial numbers. The serial numbers are assigned at an earlier time and the product is branded with it. I'm just trying to prevent someone from entering a product twice or having 2 products with the same serial.

I currently have checks for each textbox in my userform, I was thinking I could have just another check for that textbox when they try to submit the entire form... not necessarily when they try to tab off the serial number field.

To help me I'll give an example of one of my checks:

Please Login or Register  to view this content.

I was hoping I could have some coding added after that last check that looks for a duplicate serial number and prevents susbmission of the form if it exists. The data from that textbox gets stored in column A on a worksheet called ProductData. So I basically need it to check ALL of column A on that sheet and return the error if it finds a duplicate.

I tried working with some of the suggestions posted already but they are a little foreign to me... curse my underdeveloped brain! I wasn't able to get them working so I figured I'd give a little more detail to my situation in hopes that someone can give me the "VBA for Dummies" version that I can mostly copy and paste.

Or feel free to explain what each line of the code is doing, you know, all that "teach a man to fish" stuff... but you'll have it dumb it down for me if you choose that option.

**royUK** · 06-04-2011, 02:52 AM

You can use a worksheet function to checl

Please Login or Register  to view this content.

**nohero** · 06-09-2011, 08:41 AM

Ok, thanks Roy. This code worked perfectly, just needed to point it to the right sheet and column but now it's checking for me!

With what I have here now I might just split this up into 2 checks so that I can get 2 different error messages depending on what triggered the fail on the check.

Sorry for the delayed response and thank you for the help yet again.