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: 11
  • testdata.jpg
    testdata.jpg
    129.4 KB · Views: 11

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Glad to help (partially) 😁
im so sorry,what if im trying to key a new value instead?cause it show error as the value that im putting in is not yet in the data.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,313
Members
425,272
Latest member
Umba

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