Populating a form from a spreadsheet

  • Thread starter Thread starter kelly reed
  • Start date Start date
K

kelly reed

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?
 
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").value _
=.Range("a:a")(res).Offset(0, 1).Value

If you wanted to plop the value into another worksheet.
 
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?
Click to expand...

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/t...-expenditure-log-job-tracker-TC030008309.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/providers/PN030000658.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.
 
Back
Top