Vlookup in Textbox on Userform

dannyboy1982

Board Regular
Joined
Mar 6, 2015
Messages
60
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.

Code:
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
Hi,
perhaps something like this may do what you want:

Code:
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.

Code:
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

Board Regular
Joined
Mar 6, 2015
Messages
60
Thanks very much Dave. Your first example of code is exactly what I was looking for.

Regards, Dan
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top