Thanks Toppers,

Much appreciated, thats a big help!

"Toppers" wrote:

> John,
> Use something like this. The variable "MyRef" will assigned from
> your textbox i.e. MyRef=Textbox6.value
>
> You could call FillForm from the appropriate Userform macro
> (Commandbutton-Click).
>
> HTH go get you started.
>
> Sub FillForm(MyRef As String)
>
> Dim ws1 As Worksheet
> Dim RefRng As Range
>
> Set ws1 = Worksheets("Sheet1") ' <=== change as required
>
> With ws1
> lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
> Set RefRng = .Range("a2:a" & lastrow)
> ' Look for mYRef in RefRng i.e. column A
> res = Application.Match(MyRef, RefRng, 0)
> If IsError(res) Then
> MsgBox MyRef & " not found."
> Else
> refrow = res + 1 ' Assumes data starts in row 2
> ' Sample for assigning data to textbox from cell
> Userform1.Textbox1.Value = Cells(refrow, 3) ' <===puts data form
> Column C in textbox1
> ' add your code
> End If
> End With
>
> End Sub
>
> "mg_sv_r" wrote:
>
> > Hi,
> >
> > I've created a user form, for the first time ever. The form is simply a
> > series of text boxes for data input with a couple of command buttons. I can
> > fill the text boxes in and it writes the data to the correct columns on the
> > correct worksheet. Great, but its a long form and it takes a while to fill
> > out.
> >
> > Some of the data is already present in another worksheet, So to save data
> > entry time, I wanted to add a text box to allow the user to enter a reference
> > number, then add a command button that when clicked will find the reference
> > entered, by searching column A of a worksheet. Then I want the form to
> > autofill some of the other text boxes on the form with the data on the
> > worksheet from columns in the same row as the entered reference. If the
> > reference does not exist I want it to say so!
> >
> > Problem, I'm no good at this yet and whilst I can get my form to write to a
> > worksheet I cant get it to read from one and autofill my text boxes.
> >
> > Assuming this is possible, can anybody help / give me some tips? Any help
> > would be very much appreciated.
> >
> > Thanks
> >
> > John
> >