Texbox dynamic change, automatic update

[kapela2017]

Greetings, I wonder if you can guide me, I'm trying to make a texbox ("Textbox2") return me automatically ("Change Event") the result of a search ("VlooKup") based on the data of another texbox(" Textbox1"),Both texboxes are in a Userform("Userform1"), The data is in a Table("ListObjects") I tried the Control Source property without success, pay attention to your advice....

Private Sub TextBox2_Change()

Dim Tabla1 As ListObject
Set Tabla1 = ThisWorkbook.Worksheets("hoja1").ListObjects("Tabla1")
Dim codigo As Range
Set codigo = Tabla1.ListColumns("codigo").DataBodyRange
Dim nro As Range
Set nro = Tabla1.ListColumns("nro").DataBodyRange

Me.Controls("TextBox2").Value = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Tabla1.ListColumns("codigo").DataBodyRange, Tabla1.ListColumns("nro").DataBodyRange, 2, 0)
End Su

b

 

[Akuini]

The change event should be on Textbox1 not Textbox2, so it should be:

Private Sub TextBox1_Change()

Dim Tabla1 As ListObject
Set Tabla1 = ThisWorkbook.Worksheets("hoja1").ListObjects("Tabla1")
Dim codigo As Range
Set codigo = Tabla1.ListColumns("codigo").DataBodyRange
Dim nro As Range
Set nro = Tabla1.ListColumns("nro").DataBodyRange

Me.Controls("TextBox2").Value = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Tabla1.ListColumns("codigo").DataBodyRange, Tabla1.ListColumns("nro").DataBodyRange, 2, 0)
End Sub

 

[kapela2017]

The change event should be on Textbox1 not Textbox2, so it should be:

Private Sub TextBox1_Change()

Dim Tabla1 As ListObject
Set Tabla1 = ThisWorkbook.Worksheets("hoja1").ListObjects("Tabla1")
Dim codigo As Range
Set codigo = Tabla1.ListColumns("codigo").DataBodyRange
Dim nro As Range
Set nro = Tabla1.ListColumns("nro").DataBodyRange

Me.Controls("TextBox2").Value = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Tabla1.ListColumns("codigo").DataBodyRange, Tabla1.ListColumns("nro").DataBodyRange, 2, 0)
End Sub

Greetings My dear, here again in trouble, I tried it the way you propose but it generates an error, thanks for your contribution...

 

[Akuini]

What's the error?

Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Tabla1.ListColumns("codigo").DataBodyRange, Tabla1.ListColumns("nro").DataBodyRange, 2, 0)

Could you explain what you're trying to do with the VLookup?
Why do you put 2 separate columns in the argument? It won't work.

 

[kapela2017]

What's the error?

Could you explain what you're trying to do with the VLookup?
Why do you put 2 separate columns in the argument? It won't work.

Ready I solved it using XLookUp instead of Vlookup, could you advise me on, if I need it I would like to multiply this operation by the value of another texbox, what would be the correct syntax, let's say I want to multiply it by the value of the texbox?...

 

[Akuini]

Ready I solved it using XLookUp instead of Vlookup

Could you show us the code?

 

[kapela2017]

Could you show us the code?

I managed to make it work that way, it does it automatically and dynamically, I would like to know how to establish the syntax if I needed to multiply that operation by the value of a texbox("Texbox3.value")

Private Sub TextBox1_Change()

Dim Tabla1 As ListObject
Set Tabla1 = ThisWorkbook.Worksheets("hoja1").ListObjects("Tabla1")


Me.Controls("TextBox2").Value = Application.WorksheetFunction.XLookup(CLng(Me.TextBox1), Tabla1.ListColumns("codigo").DataBodyRange, Tabla1.ListColumns("nro").DataBodyRange, "", 0)
End Sub

 

[Akuini]

Try this:

Private Sub TextBox1_Change()
Call try
End Sub

Private Sub TextBox3_Change()
Call try
End Sub

Sub try()
Dim Tabla1 As ListObject
Set Tabla1 = ThisWorkbook.Worksheets("hoja1").ListObjects("Tabla1")

On Error Resume Next
Me.Controls("TextBox2").Value = CLng(Me.TextBox3) * Application.WorksheetFunction.XLookup(CLng(Me.TextBox1), Tabla1.ListColumns("codigo").DataBodyRange, Tabla1.ListColumns("nro").DataBodyRange, "", 0)
On Error GoTo 0
End Sub

 

[kapela2017]

Try this:

Private Sub TextBox1_Change()
call try
finish sub

Private Sub TextBox3_Change()
call try
finish sub

child test()
Dim Table1 as ListObject
Set Table1 = ThisWorkbook.Worksheets("sheet1").ListObjects("Table1")

On error Resume Next
Me.Controls("TextBox2").Valor = CLng(Me.TextBox3) * Application.WorksheetFunction.XLookup(CLng(Me.TextBox1), Tabla1.ListColumns("codigo").DataBodyRange, Tabla1.ListColumns("nro") .DataBodyRange, "", 0)
In case of error Go to 0
finish sub

Greetings, dear friend, I stop to thank you and inform you, that your solution worked very well, the other night sleep overcame me here in Venezuela, it was 3:30 am and I was very sleepy, I take advantage of asking you, do you have knowledge about any telegram group that Could you give guidance in VBA here the information is quite scarce and I really want to learn as much as I can, successes and blessings...

 

[Akuini]

do you have knowledge about any telegram group that Could you give guidance in VBA

Sorry, no. But I've learnt so much about Excel in this forum.

Note:
If the input number includes decimal number then you need to replace CLng with CDbl.

You're welcome, glad to help & thanks for the feedback.