Verify that record dosen't exist on user form text box entry

On a user form i'm trying to verify that the entered part number in the text box "txtPartNo" doesn't already exist in a seperate workbook (Column F holds the part number). If the entered part number does exist then i display a message via msg box that a record does exist and it then references the specific planning number via the dpti variable (the planning number is in column A) in the same message box, we would then exit the sub and user form.

If no record of the part number exists in the seperate workbook then we can continue entering the remainder of the user form info.

When i enter a part number in the text box upon enter i immediately receive a "run time error 13", Type mismatch, not sure what i'm doing wrong, anyone have a suggestion?

At the end of the day I'm just trying to ensure that we don't have duplicate part numbers in the workbook.

Please Login or Register  to view this content.

Where in the code do you get the error?

Hi jprlimey

It seems to me Column A would be Offset -5 from Column F, and you probably can't use ActiveCell.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I can't see anything wrong with that, all the arguments and the values you are setting them to seem proper and correct.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

Hi jprlimey

Wrong Column

Please Login or Register  to view this content.

Hi jprlimey

Change the first Message Box Code...

Please Login or Register  to view this content.

Hi jprlimey

It appears to work for me...see attached...

There's a way to avoid that sort of thing, add this at the top of each module.

Please Login or Register  to view this content.

With that in place you will be notified of any undeclared variables.

PS You can set VBA to automatically add Option Explicit to each module by going to Tools>Options... and selecting Require Variable Declaration on the Editor tab.

Hi jprlimey

You're welcome...glad I could help. Thanks for the Rep.

Take Norie's advice...it's what I do. Saves a LOT of heartburn.

You can set VBA to automatically add Option Explicit to each module by going to Tools>Options... and selecting Require Variable Declaration on the Editor tab.