VLookup from TextBox to TextBox

[mrsec]

Good Day
I hava a question regarding vlookup and Textbox's

Legend:
Sample Data Range:"A3 to B6"
in Userform
TextBox1 and TextBox2
Command Button

When I Enter a Value in TextBox1 = column A3 in worksheet,And Press the Command Button

Result shows in TextBox2= column B3 in worksheet.

Private Sub CommandButton1_Click()
    Dim z As Double
    z = TextBox1.Value
    TextBox2.Text = Application.WorksheetFunction.VLookup(z, Sheets("Sheet1").Range("A3:B6"), 2, False)
End Sub

My issues are;
1.Instead of Click Event can i use Change Event instead(remove command button and key directly to TextBox1)
2.Everytime I make a Name Range for my table("A3:B6") the code stop working.

 

[Zot]

Use something like this in UserForm. The macro is triggered when you press ENTER

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyReturn Then
    Dim z As Double
    z = TextBox1.Value
    TextBox2.Text = Application.WorksheetFunction.VLookup(z, Sheets("Sheet1").Range("A3:B6"), 2, False)
End If

End Sub

 

[mrsec]

Use something like this in UserForm. The macro is triggered when you press ENTER

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyReturn Then
    Dim z As Double
    z = TextBox1.Value
    TextBox2.Text = Application.WorksheetFunction.VLookup(z, Sheets("Sheet1").Range("A3:B6"), 2, False)
End If

End Sub

Thank You for the reply and code,i did apply the code to the sample workbook with sample userform and it is definitely working.
but for some reason when i use and apply it to my current data it shows error.(image attached)
In my original workbook i have 20 columns and the look up value is in column 5(E) and my column index number is in 7(G).

 

[Zot]

Make sure the sheet name existed. You are referring to table in Sheet1.

 

[mrsec]

Make sure the sheet name existed. You are referring to table in Sheet1.

I think I did.

 

[Zot]

I cannot see anything wrong at the moment. It is simple code. Did the lookup functioned ok on Excel spreadsheet?

 

[mrsec]

I cannot see anything wrong at the moment. It is simple code. Did the lookup functioned ok on Excel spreadsheet?

yeah the code works on my sample worksheet ,problem only occurs when i used it the workbook that im working on.
could be my sheet that has something on hahah.but i do thank you for the help.

 

[Zot]

If you can show your working sheet, perhaps we can find something?

 

[mrsec]

If you can show your working sheet, perhaps we can find something?

yeah no problem,how do i upload the file?

 

[Zot]

yeah no problem,how do i upload the file?

You can use DropBox w=or whatever free there but in this case probably just capture the sheet (region of interest perhaps)

It is adviseable in future to use Xl2BB (the icon on the right most) to install Add-In that enable helpers out there to get actual sheet.