VBA to check if Data validation selections are not duplicated

I think having an example would help, I'm a bit confused by your explanation. But a very basic example of how to look for a duplicate value:

Please Login or Register  to view this content.

Sorry here is the sheet I just don't want the user to be able and use the same drop down selection in column B on the Daily Chgs Tab

I have alot of things being added to this so this is just one of many threads I have to start.

Let me know if this helps.

I had an error I changed and added 'Dim S as Variant'

Please Login or Register  to view this content.

it had given me a variable not defined.

Do you know how to add to the code so it looks at that range and counts all the rows that have data in them and tell if it is greater than 45 lines...if so have mess box tell them to modify entries to only have 45 selections for the destination sheet.

I will have more input rows than the destination sheet will allow if the user fills in all drop downs.



Or even better can it be set up to ... if the user does have more input than 45 rows in the column than it will add to the destination sheet inserting that number over 45 into the sheet starting at row 52 on the Job ticket tab and still keep all formatting

How would I change it to not only look for the duplicate but also not allow the duplicate to be used
Right now you can still use the duplicate I would like it to block user from using it if possible