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.

Private Sub txtPartNo_AfterUpdate()

Dim Src As Workbook
Dim Dest As Workbook
Dim iRow As Long
Dim ws As Worksheet
Dim rng As Range
Dim rng1 As Range

Set Src = ThisWorkbook  'Source Workbook
Set Dest = Workbooks.Open("Test Log.xlsm")
Set ws = Dest.Worksheets("Test")

Set rng1 = ws.Columns(6).Find( _
    what:=txtPartNo.Value, _
    after:=ws.Cells(Rows.Count, 1), _
    LookIn:=xlValues, _
    Lookat:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)

If Not rng1 Is Nothing Then
    dpti = ActiveCell.Offset(0, -6).Value
     MsgBox "Material Number already Exists, check  & dpti .  ", vbExclamation, "Material Number Already Exists"
    Exit Sub
Else
    MsgBox "Material number is valid", vbExclamation, "Material Number Valid"
End If

Workbooks("DTPI Log.xlsm").Close SaveChanges:=False

End Sub

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.

Range("F" & rng1.row).Offset(0, -5).Value

ActiveCell.Offset(0, -5).Value

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

 after:=ws.Cells(Rows.Count, 2)

Hi jprlimey

Change the first Message Box Code...

If Not rng1 Is Nothing Then
    dtpi = ws.Range("B" & rng1.Row).Offset(0, -1).Value
    MsgBox "Material Number already Exists, check " & dtpi & ".  ", vbExclamation, "Material Number Already Exists"
    Exit Sub
Else
    MsgBox "Material number is valid", vbExclamation, "Material Number Valid"
End If

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

Option Explicit

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.