Transfer Textbox to Sheet based on Value of 2 Other Textboxes

[rhombus4]

Hi

Trying to Transfer a Textbox to a Sheet based on its Value and the Value of 2 Other Textboxes (1 for Row and 2 for Column)

I have 3 Textboxes Textbox1 and Textbox2 and Textbox3

Textbox1 contains a Value to be Searched in Column D
Textbox2 Contains a Value which will be one of the Headings in Row 1
Textbox3 is the Value to be transferred

So If Textbox 1 is Cat and the Value in Textbox 2 is Colour and the Value in Textbox3 is Yellow
I want Yellow to go into G4 (G is where the Colour Heading is and 4 is the Row where Cat is

	A	B	C	D	E	F	G	H	I	J	K
1​				Animal	Country	Vegetable	Colour
2​				Aardvark	Angola	Apple	Amber
3​				Bear	Barbados	Banana	Blue
4​				Cat	Chile	Cherry	Cerise
5​				Dog	Denmark	Date	Dark Blue
6​				Elephant	England	Eggplant	Emerald Green
7​				Fox	France	Fig	Fuschia
8​				Goat	Greece	Grape	Gold
9​
10​

 

[GWteB]

See if this works for you. Code to be pasted in the module of the worksheet involved (right click on worksheet tab, click View Code to open the VBE, paste code in right hand pane and close VBE).

EDIT:
If your textboxes are on a userform code can be pasted in its code behind module. Note that the code then will target te active worksheet.

Option Explicit

Private Type TLocals
    AnimalRow       As Long
    CategoryColumn  As Long
End Type
Private this As TLocals

Private Sub TextBox1_Change()
    ' Animal in column D
    Dim d As Range, r As Range
    this.AnimalRow = 0
    Set d = Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    Set r = d.Find(Trim$(Me.TextBox1.Value), , , xlWhole, , , False)
    If Not r Is Nothing Then
        this.AnimalRow = r.Row
    End If
    TextBox3_Change     ' <<<< remove this line if a transfer should only take place when box 3 is changed.
End Sub

Private Sub TextBox2_Change()
    ' category in row 1
    Dim d As Range, r As Range
    this.CategoryColumn = 0
    Set d = Range(Cells(1, "E"), Cells(1, Columns.Count).End(xlToLeft))
    Set r = d.Find(Trim$(Me.TextBox2.Value), , , xlWhole, , , False)
    If Not r Is Nothing Then
        this.CategoryColumn = r.Column
    End If
    TextBox3_Change     ' <<<< remove this line if a transfer should only take place when box 3 is changed.
End Sub

Private Sub TextBox3_Change()
    ' value to be copied
    If this.AnimalRow <> 0 And this.CategoryColumn <> 0 Then
        Cells(this.AnimalRow, this.CategoryColumn).Value = TextBox3.Value
    End If
End Sub

 

[rhombus4]

Thanks. I'll have a look later

Forgot to mention it's on a userform and wanted the transfer to happen when a command button is pressed

 

[rhombus4]

Also textbox 1 and 2 will already filled in based on users previous selections within the form

Effectively on command button I need

Worksheets("Sheet2").range("G4") = .Value = TextBox3.Value

Just need to get the G4 based on what's is in textbox1 for the column(g) and what's in textbox2 for the row (4) as in example in post 1

Textbox 1 is Cat, Textbox 2 is Colour and the Value in Textbox3 is Yellow so G4 will become yellow

Apologies for any confusion

 

[GWteB]

No confusion on this side, just wanted to be clear.

Effectively on command button I need

Worksheets("Sheet2").range("G4") = .Value = TextBox3.Value

The code above wil give you a TRUE or a FALSE in G4, provided there's a preceding With statement to qualify the .Value (otherwise it will error ...), so I followed your explanation.
Made a few amendments to suit use on a userform. Please note the procedure names to be renamed.

Option Explicit

Private Type TLocals
    TargetSheet     As Worksheet
    AnimalRow       As Long
    CategoryColumn  As Long
End Type
Private this As TLocals

Private Sub TextBox1_Change()
    ' Animal in column D
    Dim d As Range, r As Range
    If Not this.TargetSheet Is Nothing Then
        this.AnimalRow = 0
        Set d = this.TargetSheet.Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        Set r = d.Find(Trim$(Me.TextBox1.Value), , , xlWhole, , , False)
        If Not r Is Nothing Then
            this.AnimalRow = r.Row
        End If
    End If
End Sub

Private Sub TextBox2_Change()
    ' category in row 1
    Dim d As Range, r As Range
    If Not this.TargetSheet Is Nothing Then
        this.CategoryColumn = 0
        Set d = this.TargetSheet.Range(Cells(1, "E"), Cells(1, Columns.Count).End(xlToLeft))
        Set r = d.Find(Trim$(Me.TextBox2.Value), , , xlWhole, , , False)
        If Not r Is Nothing Then
            this.CategoryColumn = r.Column
        End If
    End If
End Sub

Private Sub Commandbutton_Click()                               ' <<<< rename to suit
    ' value to be copied
    If Not this.TargetSheet Is Nothing Then
        If this.AnimalRow <> 0 And this.CategoryColumn <> 0 Then
            this.TargetSheet.Cells(this.AnimalRow, this.CategoryColumn).Value = Me.TextBox3.Value
        End If
    End If
End Sub

Private Sub Userform_Initialize()                               ' <<<< rename to suit
    Set this.TargetSheet = ThisWorkbook.Worksheets("Sheet2")    ' <<<< change sheet to suit
End Sub

 

[rhombus4]

Worksheets("Sheet2").range("G4") = .Value = TextBox3.Value was a typo should have been

Worksheets("Sheet2").range("G4").Value

 = TextBox3.Value

Is it possible to get all the code in the command button using value of textbox1 as the column and textbox2 as the row

Thanks

 

[GWteB]

Is it possible to get all the code in the command button using value of textbox1 as the column and textbox2 as the row

I'm not sure what you mean by this, but if you would mean "Is it possible to put all the code in the command button's click event handler" then the answer is yes. But before we go any further with this, does the following still apply?

So If Textbox 1 is Cat and the Value in Textbox 2 is Colour and the Value in Textbox3 is Yellow
I want Yellow to go into G4 (G is where the Colour Heading is and 4 is the Row where Cat is

 

[rhombus4]

Yes the above applies.

When a command button is clicked the value in textbox3 will go into a cell on Sheet2

The column is based on value in textbox1 and row based on textbox2 which will already be filled in

 

[GWteB]

Ok, how about ...

Option Explicit

Private Type TLocals
    TargetSheet     As Worksheet
    AnimalRow       As Long
    CategoryColumn  As Long
End Type
Private this As TLocals

Private Sub Userform_Initialize()                               ' <<<< rename to suit
    Set this.TargetSheet = ThisWorkbook.Worksheets("Sheet2")    ' <<<< change sheet to suit
End Sub

Private Sub Commandbutton_Click()                               ' <<<< rename to suit
    Dim d As Range, r As Range
    If Not this.TargetSheet Is Nothing Then
        With this.TargetSheet
            this.AnimalRow = 0
            this.CategoryColumn = 0
            ' Animal in column D (Tbx 1)
            Set d = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            Set r = d.Find(Trim$(Me.TextBox1.Value), , , xlWhole, , , False)
            If Not r Is Nothing Then
                this.AnimalRow = r.Row
            End If
            ' Category in row 1 (Tbx 2)
            Set r = Nothing
            Set d = .Range(.Cells(1, "E"), .Cells(1, .Columns.Count).End(xlToLeft))
            Set r = d.Find(Trim$(Me.TextBox2.Value), , , xlWhole, , , False)
            If Not r Is Nothing Then
                this.CategoryColumn = r.Column
            End If
            ' copy to sheet (Tbx 3)
            If this.AnimalRow <> 0 And this.CategoryColumn <> 0 Then
                .Cells(this.AnimalRow, this.CategoryColumn).Value = Me.TextBox3.Value
            End If
        End With
    End If
End Sub

 

[rhombus4]

Thanks, all versions above worked and the last one as needed via command button