Vlookup in Textbox on Userform

[dannyboy1982]

Hi,

I have a simple userform that allows the user to enter a part number, a Vlookup then looks in a table of data and returns a value associated with that part number. What I would like is a message box to appear when an invalid part number is entered saying something like "Invalid part number, please try again". I have managed to get the message box to appear, but it is displayed every time a part number is entered - correct or otherwise. Can a anyone shed a light on what I am doing wrong? Many thanks.

Sub Commandbutton2_click()
On Error GoTo out
TextBox2.Value = Application.VLookup(TextBox1.Value, Sheets("Pivot table").Range("A5:G500"), 7, False)
If IsNumeric(Me.TextBox2.Value) Then
    Me.TextBox2.Value = Format(TextBox2.Value, "0.00")
    End If
out:
MsgBox "Try again - Job not found"
End Sub

 

[dmt32]

Hi,
perhaps something like this may do what you want:

Sub Commandbutton2_click()
        Dim found As Variant
        Dim search As String
    
        search = Me.TextBox1.Value
    
    
        found = Application.VLookup(search, Sheets("Pivot table").Range("A5:G500"), 7, False)
        
            If Not IsError(found) Then
            
                    Me.TextBox2.Value = Format(found, "0.00")
                
                Else
                
                    MsgBox search & Chr(10) & "Job not found", 48, "Not Found"
                
            End If
End Sub

If your part numbers are numeric only then you can apply that test before you do the lookup.

Sub Commandbutton2_click()
        Dim found As Variant
        Dim search As String
    
        search = Me.TextBox1.Value
    
        If IsNumeric(search) Then
        
        found = Application.VLookup(CLng(search), Sheets("Pivot table").Range("A5:G500"), 7, False)
        
            If Not IsError(found) Then
            
                    Me.TextBox2.Value = Format(found, "0.00")
                
                Else
                
                    MsgBox search & Chr(10) & "Job not found", 48, "Not Found"
                
            End If
            
            Else
                MsgBox search & Chr(10) & "Numeric Values Only", 48, "Numeric Only"
        
        End If
End Sub

Dave

 

[dannyboy1982]

Thanks very much Dave. Your first example of code is exactly what I was looking for.

Regards, Dan

 

[dmt32]

Thanks very much Dave. Your first example of code is exactly what I was looking for.

Regards, Dan

Most welcome glad solution helped.

Many thanks for feedback

Dave