Auto fill form with data from worksheet

[mg_sv_r]

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

[Toppers]

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
>

[mg_sv_r]

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
> >