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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,332
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
7,332
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,158
Messages
5,835,718
Members
430,383
Latest member
Kastore

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