VLookup from TextBox to TextBox

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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.

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

Attachments

  • testuserform.jpg
    testuserform.jpg
    118.7 KB · Views: 13
  • testdata.jpg
    testdata.jpg
    129.4 KB · Views: 13

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,002
Office Version
  1. 2016
Platform
  1. Windows
Use something like this in UserForm. The macro is triggered when you press ENTER
VBA Code:
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
 
Solution

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Use something like this in UserForm. The macro is triggered when you press ENTER
VBA Code:
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).
 

Attachments

  • error.jpg
    error.jpg
    14.8 KB · Views: 9
Last edited:

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,002
Office Version
  1. 2016
Platform
  1. Windows
Make sure the sheet name existed. You are referring to table in Sheet1.
 

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Attachments

  • sheet data.jpg
    sheet data.jpg
    187.4 KB · Views: 14

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,002
Office Version
  1. 2016
Platform
  1. Windows
I cannot see anything wrong at the moment. It is simple code. Did the lookup functioned ok on Excel spreadsheet?
 

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
2,002
Office Version
  1. 2016
Platform
  1. Windows
If you can show your working sheet, perhaps we can find something?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,002
Office Version
  1. 2016
Platform
  1. Windows
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.
 
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,151,821
Messages
5,766,638
Members
425,364
Latest member
jstubs99

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