Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a form from a spreadsheet
Is there a way to enter an ID number in a form then make excel look on
a specific spreadsheet for that number, then pull the information in that line into another spreadsheet? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a form from a spreadsheet
Yep.
If there is a key column (say column A) on Sheet1 that contains that value, you could use something like this. This takes the value in Textbox1 of the userform, and matches it against column A of Sheet1. If there's a match, then it puts the value in column B into textbox2 of the userform. If there's no match, it puts a warning message into textbox2. Option Explicit Private Sub CommandButton1_Click() Dim res As Variant Dim wks As Worksheet Dim IDNumber As Long If IsNumeric(Me.TextBox1.Value) = False Then MsgBox "Please enter an ID!" Exit Sub End If Set wks = Worksheets("sheet1") IDNumber = CLng(Me.TextBox1.Value) With wks res = Application.Match(IDNumber, .Range("A:a"), 0) If IsError(res) Then 'no match found, what should happen Else 'put the value from column B into textbox2 of the userform Me.TextBox2.Value = .Range("a:a")(res).Offset(0, 1).Value End If End With End Sub I'm kind of confused about if you wanted to populate a userform or plop data into another worksheet, but this line could change pretty easily: Me.TextBox2.Value = .Range("a:a")(res).Offset(0, 1) to worksheets("OtherSheetNameHere").range("x99").valu e _ =.Range("a:a")(res).Offset(0, 1).Value If you wanted to plop the value into another worksheet. On 07/12/2010 16:53, kelly reed wrote: Is there a way to enter an ID number in a form then make excel look on a specific spreadsheet for that number, then pull the information in that line into another spreadsheet? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a form from a spreadsheet
On Mon, 12 Jul 2010 14:53:30 -0700 (PDT), kelly reed
wrote: Is there a way to enter an ID number in a form then make excel look on a specific spreadsheet for that number, then pull the information in that line into another spreadsheet? My time tracking spreadsheets do just that with the user entered "job numbers" or other alpha-numeric string. It looks up that string, and returns the description. Though it could just as easily return entire tables of data. http://office.microsoft.com/en-us/te...030008309.aspx Note that data entered into the info workbook under "job number" and "description" show up on the time sheet when selected from the drop down list. https://office.microsoft.com/en-us/p...030000658.aspx The football sheet also does a lot of lookups My DVD database does it by a serial number for each film, but I do not have that uploaded anywhere yet. It is pretty big in size. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating a form | Excel Worksheet Functions | |||
Populating form dropdown at run time | Excel Programming | |||
Populating form with data from worksheet | Excel Programming | |||
Dynamically populating textboxes on a form | Excel Programming | |||
populating a combo box on form inilialization | Excel Programming |