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" wrote in message
...
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