Populating data on Forms from spreadsheet data
 

I would like to know how to populate data into a userform from a
worksheet.

I have data in columns B through AE, with a header column in A.
I want the user to fill in the box at the top of the form, have the
code search down column A of the spreadsheet and then return the data
from columns B through AE into their respective textboxes on the form.

Anyone??

Kristen


--
Kristen
------------------------------------------------------------------------
Kristen's Profile: http://www.excelforum.com/member.php...o&userid=33199
View this thread: http://www.excelforum.com/showthread...hreadid=531686

Populating data on Forms from spreadsheet data
 

Hello Kristen
Here is one way, TxtSearch is a textbox where you would input your criteria
(please amend accordingly)
HTH
Cordially
Pascal

Private Sub CommandButton1_Click()
On Error GoTo NotFound
ToFind = Application.Match(TxtSearch.Value, Range("A2:A5"), 0)
For i = 2 To 5
With Me.Controls("Textbox" & i)
..Value = WorksheetFunction.Index(Range("A2:A5").Offset(0, i - 1), ToFind, 1)
End With
Next i
Exit Sub

NotFound:
MsgBox "No match found", vbInformation, "Search result"
End Sub


"Kristen" a écrit
dans le message de news:
...

I would like to know how to populate data into a userform from a
worksheet.

I have data in columns B through AE, with a header column in A.
I want the user to fill in the box at the top of the form, have the
code search down column A of the spreadsheet and then return the data
from columns B through AE into their respective textboxes on the form.

Anyone??

Kristen


--
Kristen
------------------------------------------------------------------------
Kristen's Profile:
http://www.excelforum.com/member.php...o&userid=33199
View this thread: http://www.excelforum.com/showthread...hreadid=531686

Populating data on Forms from spreadsheet data
 

Thank Pascal, at least I don't get an error now :cool:

This is what I changed it to.... I'm not the most skilled at VBA so I'm
not sure what everything does. I created a button named "Search", and
when I click it, it looks in a text box called "PartNo". Then it is
supposed to search in a pivot table on a sheet called SortedData. I
entered a the first part number in the partno box and it tells me that
a match is not found. The part number listing starts in box A5. Now
what?

Private Sub Search_Click()

On Error GoTo NotFound
ToFind = Application.Match(PartNo.Value, Range("A5:A999"), 0)
For i = 2 To 5
With Me.Controls("Part Number" & i)
PartNo.Value = WorksheetFunction.Index(Range("A5:A999").Offset(0, i-1),
ToFind, 1)
End With
Next i
Exit Sub

NotFound:
MsgBox "No match found", vbInformation, "Search result"
End Sub


--
Kristen
------------------------------------------------------------------------
Kristen's Profile: http://www.excelforum.com/member.php...o&userid=33199
View this thread: http://www.excelforum.com/showthread...hreadid=531686

Populating data on Forms from spreadsheet data
 

Hello Kristen
Values in textboxes are text strings by design so you need to amend your
code:
ToFind = Application.Match(Clng(PartNo.Value), Range("A5:A999"), 0)

HTH
Cordially
Pascal

"Kristen" a écrit
dans le message de news:
...

Thank Pascal, at least I don't get an error now :cool:

This is what I changed it to.... I'm not the most skilled at VBA so I'm
not sure what everything does. I created a button named "Search", and
when I click it, it looks in a text box called "PartNo". Then it is
supposed to search in a pivot table on a sheet called SortedData. I
entered a the first part number in the partno box and it tells me that
a match is not found. The part number listing starts in box A5. Now
what?

Private Sub Search_Click()

On Error GoTo NotFound
ToFind = Application.Match(PartNo.Value, Range("A5:A999"), 0)
For i = 2 To 5
With Me.Controls("Part Number" & i)
PartNo.Value = WorksheetFunction.Index(Range("A5:A999").Offset(0, i-1),
ToFind, 1)
End With
Next i
Exit Sub

NotFound:
MsgBox "No match found", vbInformation, "Search result"
End Sub


--
Kristen
------------------------------------------------------------------------
Kristen's Profile:
http://www.excelforum.com/member.php...o&userid=33199
View this thread: http://www.excelforum.com/showthread...hreadid=531686