Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use the X button on a userform | Excel Discussion (Misc queries) | |||
Userform question | Excel Discussion (Misc queries) | |||
Data Validation Cell - Move to UserForm | Excel Worksheet Functions | |||
Cell Content from UserForm Not Retained | Excel Discussion (Misc queries) | |||
How can I run a macro in the background whilst a UserForm is visib | Excel Discussion (Misc queries) |