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

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

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