View Full Version : Solved: AutoFill TextBox in UserForm
msquared99
07-24-2012, 11:19 AM
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
07-24-2012, 12:14 PM
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
07-25-2012, 06:27 AM
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
07-25-2012, 08:12 AM
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
07-26-2012, 08:15 AM
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
07-26-2012, 08:16 AM
Here is the source file.
Thanks for the help.
Tinbendr
07-26-2012, 11:45 AM
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
07-26-2012, 02:23 PM
I finally figured it out using search and find.
Ken's code also helped me out with another function.
Thanks all!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.