Retrieving Data from a worksheet to auto fill a user form

Hi,

Im very new to all this so forgive me if this sounds stupid!

I have made my first ever user form and this works well and inserts data
into a worksheet without problems.

A lot of the data that is input may already be on another worksheet. Can I
add a textbox and command button to allow the user to specify a reference
number? Then have the app look on the other worksheet for the reference and
use the values it finds to autofill some of the userform text boxes, in much
the same way that I could use Vlookup on the worksheet itself?

Thanks in advance for any help.

regards

John

this should be close to what you want. You will need to speicfy the name of
the sheet and the column you want to seach. Replace "This" with TextBox1.text
or whatever...

Public Sub FindMatch()
Dim rngToSearch As Range
Dim wksToSearch As Worksheet
Dim rngFound As Range

Set wksToSearch = Sheets("Sheet1")
Set rngToSearch = wksToSearch.Columns("A")
Set rngFound = rngToSearch.Find("This", , xlConstants, xlWhole)
If rngFound Is Nothing Then
MsgBox "Sorry, Not Found"
Else
MsgBox rngFound.Value
MsgBox rngFound.Offset(0, 1).Value
MsgBox rngFound.Offset(0, 2).Value
End If

End Sub
--
HTH...

Jim Thomlinson


"mg_sv_r" wrote:

> Hi,
>
> Im very new to all this so forgive me if this sounds stupid!
>
> I have made my first ever user form and this works well and inserts data
> into a worksheet without problems.
>
> A lot of the data that is input may already be on another worksheet. Can I
> add a textbox and command button to allow the user to specify a reference
> number? Then have the app look on the other worksheet for the reference and
> use the values it finds to autofill some of the userform text boxes, in much
> the same way that I could use Vlookup on the worksheet itself?
>
> Thanks in advance for any help.
>
> regards
>
> John
>
>