Solved: AutoFill TextBox in UserForm

[msquared99]

I've been beating my brains out on this one since I'm new to VBA.

I have a UserForm that has several TextBoxes in it.

The user will enter an ID# in TextBox1, then TextBox2 and so on will be AutoFilled by taking data from a separate workbook called PR 1461 Data. In the PR 1461 Data workbook the ID# is in column A and the data to populate the other TextBoxes in the UserForm is in columns B thru I.

What is the code needed to accomplish this?

Thanks for any help!

[CatDaddy]

something like this? i dont know how you are getting to the row of info but you would probably substitute that for the 1

For i = 2 to 8
TextBox("TextBox" & i-1).Value = Sheets(1).Cells(1,i).Value
Next i

[msquared99]

Here is what I have so far:

Option Explicit
Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub tbCOID_Change()
If Not IsDate(tbCOID) Then
Me.tbCOID = Format(Me.tbCOID, "0000")
Else
MsgBox "Input must be a number in the format 0000"
End If
End Sub

Private Sub tbDate_Initialize()
Me.tbDate.Value = Format(Date, "mm/dd/yy")
End Sub

Private Sub tbName_Change()
Dim rng As Range
Dim cel As String

Workbooks.Open ("C:\Mike M\2012 Files\VBA Projects\PR 1461 Results.xls")
Set wkbdata = ActiveWorkbook
Set rng = wkbdata("PR 1461 Results").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
cel = frmSales.tbCOID.Value

tbName.Value = Application.VLookup(Me.tbCOID, wkbdata("PR 1461 Results").Range("A:J"), 2, False)

wkbdata.Close SaveChanges:=False
End Sub

All I am trying to do is lookup the value of tbCOID and place the name in tbName from the workbook PR 1461 Results.

Problem is I have never worked with UserForms before.

Thanks for your help.

[Kenneth Hobs]

Please use VBA code tags.

While you could use a WorksheetFunction, you could just use the range object's Find Method. Once the range found is returned, use the found range's Offset method or Cells method as CatDaddy explained.

A short example file makes helping easier for us and usually provides a means for a more exact solution.

You have some problems with your code as is. Maybe something like:
Private Sub tbName_Change()
Dim rng As Range, cel As String
Dim wkbData As Workbook, f As Range

Set wkbData = Workbooks.Open("C:\Mike M\2012 Files\VBA Projects\PR 1461 Results.xls")
Set rng = wkbData.Worksheets("Sheet1").Range("A2", wkbData.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))

cel = tbCOID.Value
'tbName.Value = Application.VLookup(Me.tbCOID, wkbData("PR 1461 Results").Range("A:J"), 2, False)
Set f = rng.Find(cel, LookAt:=xlValues)
If f Is Nothing Then GoTo EndSub
tbName.Value = f.Value

'offset example:
tbOther.Value = f.Offset(0, 1).Value
'etc.

EndSub:
wkbData.Close SaveChanges:=False
End Sub

[msquared99]

Attached is the master file.

I tried using the code from Kenneth and received an error on If f Is Nothing Then GoTo EndSub

I'll attach the source file in another post.

I think I've just pulled my last hair out!

[msquared99]

Here is the source file.

Thanks for the help.

[Tinbendr]

Crossposted! (http://www.excelforum.com/excel-programming-vba-macros/848159-vba-userform-have-entry-in-textbox-fill-another-textbox-by-vlookup.html)

Please observe forum rules. (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3)

Etiquette for crossposting. (http://www.excelguru.ca/content.php?184)

[msquared99]

I finally figured it out using search and find.

Ken's code also helped me out with another function.

Thanks all!