Autofill Userform Textboxes with Data on Spreadsheet

[fun4all]

Hello,

I know how to fill in a spreadsheet with data I input into a userform, but how do I do the opposite?

How do I type in an entry from column A into a textbox1 and have textbox2 and textbox3 to fill with data from columns B and C respectively?



Thanks for your help!

 

[TKB]

Hello,

For textbox1, go into its macro sub and input the following:

Private Sub TextBox1_Change()


Range("[COLOR=#ff0000]A49[/COLOR]").Value = TextBox1.Value


TextBox2.Value = Range("[COLOR=#ff0000]A50[/COLOR]").Value


TextBox3.Value = Range("[COLOR=#ff0000]A51[/COLOR]").Value


End Sub

This code references generic cells in column A. You can change the referenced cell by changing the text in red.

 

[BrianExcel]

To have it fill more dynamically, you can also set the box you are typing in as the target. Then write code that loops through the column or cell you are entering searching each cell in the range for the "target" value. When it finds it, then just define what you want the textboxes filled with.

For example, if your target was found in cell A1, and you wanted to fill textbox 2 with the column NEXT to A1, then you would write:

textbox2 = target.offset(0, 1)

Using this method ensures you're always looking at the same data.

 

[fun4all]

Hello again,

Sorry for the delayed response. I attempted Brian's method because if was more of what I was looking for.

But I ran into a problem executing the code. I have the following code:

Private Sub CODE_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim range As range, range1 As range, res As Variant
Set range = Worksheets("2012").range("A1:P1000").Columns(1)
res = Application.Match(CODE.Text, range, 0)
If Not IsError(res) Then
Set range1 = range(res)

COMPONENT.Text = range1.Offset(0, 2)
CRITERIA.Text = range1.Offset(0, 3)
ITEM.Text = range1.Offset(0, 1)
Else
MsgBox "Unable to find entry in Database"

End If

End Sub

I receive a Type mismatch for the variables CRITERIA, COMPONENT, and ITEM, but all 3 should receive alphanumeric data from their respective columns on the spreadsheet.

All 3 variables are textboxes

Could I get some help figuring out what the problem is?


Thank You