Using vlookup in vba but updating the userform live

Question

I have an userform with a few texbox and i want that when you put the value on the first one scan the data sheet and if it found the same value it fill the rest of textboxes, i think i can manage to do it if i do it in two steps. First take the value of textbox1 as variable and with it scan the data an generate the second userform with the data already paste on it. But is there a way to do it live? at the moment i put data in textbox1 shows the data of the rest of the columns on the others textboxes?

Also i was trying to do more or less the same thing in a sheet with an vlookup formula (VLOOKUP(A27,BDD!A:B,2,FALSE)) and it worked but the problem is that i want the formula to change the value of the cells only in case it found a mach in the data and also i don't want the formula in the cells i want to change so i can put new data without problem.

Lets say i have in the sheet "bdd" numbers in the first columns and names in the second. I want in another sheet to put a number and if that number already exist in the bdd i want to have the name near to it but i dont want the formula near to the numbers because i want to be able to put new numbers and names if necessary.

bdd:

101 Antonio
102 Luis

sheet

101 Antonio (At the moment i finish writing 101)

103 Peter (nothing happens because it isn't in the bdd yet and i have to type peter to complete that line or put 103 peter in the bdd)

Sorry if it wasn't clear i tried :P Thanks in advance

Praveen Behera Praveen Behera · Accepted Answer · 2017-02-08T06:14:02

If data is in this range

Private Sub TextBox1_Change()

'Skip if value is not there
On Error Resume Next

'take any random cell to store textbox1's value

Range("C4").Value = TextBox1.Text

'Vlookup to get the value
TextBox2.Text = Application.WorksheetFunction.VLookup(Range("C4").Value,Sheet1.Range("A:B"), 2, 0)

End Sub

Using vlookup in vba but updating the userform live

bdd:

sheet

1 Answers