Userform

I am using a userform which shows me certain information which I would like
to transfer to a worksheet in my workbook. What I am seeking advise for is
I need help with a code to look up the value in textbox.1 and to look up the
same value in column "a" and then copy values from tb2 & 3 into columns b &
c.

Any help would be great

Thanks

Greg

I created a small userform with 3 textboxes and two buttons (ok/cancel) and one
label.

I had this code behind it:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim res As Variant
Dim myRng As Range
Dim wks As Worksheet

Set wks = ThisWorkbook.Worksheets("Sheet1")
Set myRng = wks.Range("a:a")

res = Application.Match(Me.TextBox1.Value, myRng, 0)

If IsError(res) Then
With Me.Label1
.Caption = "Not found in that range!"
.ForeColor = &HFF&
.Font.Bold = True
End With
Else
Me.Label1.Caption = ""
myRng(res).Offset(, 1).Value = Me.TextBox2.Value
myRng(res).Offset(, 2).Value = Me.TextBox3.Value

'clear out the TB's???
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
End If
End Sub
Private Sub UserForm_Initialize()
Me.Label1.Caption = ""
End Sub

The value in the textbox is a string. If you have numbers/dates/times in that
column to match up, you'll want to convert the string to a number/date/time.

===
Since you're matching up on what's in Textbox1, would you want to use a combobox
instead of a textbox for that first input. (I'd rather use the dropdown and
point and click than type an entry!)


Greg B wrote:
>
> I am using a userform which shows me certain information which I would like
> to transfer to a worksheet in my workbook. What I am seeking advise for is
> I need help with a code to look up the value in textbox.1 and to look up the
> same value in column "a" and then copy values from tb2 & 3 into columns b &
> c.
>
> Any help would be great
>
> Thanks
>
> Greg

--

Dave Peterson

Thank you Dave

Greg
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I created a small userform with 3 textboxes and two buttons (ok/cancel)
and one
> label.
>
> I had this code behind it:
>
> Option Explicit
> Private Sub CommandButton1_Click()
> Unload Me
> End Sub
> Private Sub CommandButton2_Click()
> Dim res As Variant
> Dim myRng As Range
> Dim wks As Worksheet
>
> Set wks = ThisWorkbook.Worksheets("Sheet1")
> Set myRng = wks.Range("a:a")
>
> res = Application.Match(Me.TextBox1.Value, myRng, 0)
>
> If IsError(res) Then
> With Me.Label1
> .Caption = "Not found in that range!"
> .ForeColor = &HFF&
> .Font.Bold = True
> End With
> Else
> Me.Label1.Caption = ""
> myRng(res).Offset(, 1).Value = Me.TextBox2.Value
> myRng(res).Offset(, 2).Value = Me.TextBox3.Value
>
> 'clear out the TB's???
> Me.TextBox1.Value = ""
> Me.TextBox2.Value = ""
> Me.TextBox3.Value = ""
> End If
> End Sub
> Private Sub UserForm_Initialize()
> Me.Label1.Caption = ""
> End Sub
>
> The value in the textbox is a string. If you have numbers/dates/times in
that
> column to match up, you'll want to convert the string to a
number/date/time.
>
> ===
> Since you're matching up on what's in Textbox1, would you want to use a
combobox
> instead of a textbox for that first input. (I'd rather use the dropdown
and
> point and click than type an entry!)
>
>
> Greg B wrote:
> >
> > I am using a userform which shows me certain information which I would
like
> > to transfer to a worksheet in my workbook. What I am seeking advise for
is
> > I need help with a code to look up the value in textbox.1 and to look up
the
> > same value in column "a" and then copy values from tb2 & 3 into columns
b &
> > c.
> >
> > Any help would be great
> >
> > Thanks
> >
> > Greg
>
> --
>
> Dave Peterson