Text from TextBox to specific Cells

[Stormania94]

Hello,

There is any way to replicate the below by using VBA, Macros or any other functions from Excel?

Basically, I would like that the text from first Text Box to be inserted into cell A2, A3, A4... (one at the time). And the each new line of text from second Text Box to be inserted into B2, C2, D2, E2.
And when pressing the button 'Add question' just triggering the action of adding the text to the specific cells.

Much appreciated if there is possible to do this in Excel.
Thanks.

 

[Stormania94]

I have managed to do something like that:

And is going to append the column cells with text real-time.

Private Sub TextBox1_Change()
    Dim Arr As Variant
    Dim i As Long
    Dim Target As Range

    Arr = Split(ActiveSheet.TextBox1.Text, vbLf)
    Set Target = Range("B2")
    For i = LBound(Arr) To UBound(Arr)
        Target.Value = Arr(i)
        Set Target = Target.Offset(0, 1)
    Next i
End Sub

 

[bbrnx19]

As requested Appending single Questions & Multiply Answers in next row when Add button when press.
Note using CRTL+ENTER in TextBox2 for next answer

Sub AddQ()
'
' Add Button Assgin Macro to AddQ
' Add TextBox1 , Question
' Add TextBox2 , Answers
'
LastRow = Range("a1").CurrentRegion.Rows.Count

NumAnsLines = UBound(Split(ActiveSheet.TextBox2.Text, Chr(10))) ' Chr(10) or vbLf

Range("A" & LastRow + 1).Value = ActiveSheet.TextBox1.Text

Range(Cells(LastRow + 1, 2), Cells(LastRow + 1, 2 + NumAnsLines)).Value = _
Split(ActiveSheet.TextBox2.Text, Chr(10))

End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Text from TextBox to specific Cells
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Stormania94]

As requested Appending single Questions & Multiply Answers in next row when Add button when press.
Note using CRTL+ENTER in TextBox2 for next answer

Sub AddQ()
'
' Add Button Assgin Macro to AddQ
' Add TextBox1 , Question
' Add TextBox2 , Answers
'
LastRow = Range("a1").CurrentRegion.Rows.Count

NumAnsLines = UBound(Split(ActiveSheet.TextBox2.Text, Chr(10))) ' Chr(10) or vbLf

Range("A" & LastRow + 1).Value = ActiveSheet.TextBox1.Text

Range(Cells(LastRow + 1, 2), Cells(LastRow + 1, 2 + NumAnsLines)).Value = _
Split(ActiveSheet.TextBox2.Text, Chr(10))

End Sub

View attachment 54755

Thank you for your solution @bbrnx19.

There is any way to freeze/lock them in place when I scroll down the spreadsheet?

 

[Stormania94]

I have managed doing that by freezing the top row including the form controls. There is any other way doing this?

 

[bbrnx19]

You could have a user form ie insert a UserForm module add the same components and have it trigger when the sheet is activated,
this will give you a floating form

user form code

Private Sub CommandButton1_Click()
LastRow = Range("a1").CurrentRegion.Rows.Count

NumAnsLines = UBound(Split(TextBox2.Text, Chr(10))) ' Chr(10) or vbLf

    ActiveSheet.Range("A" & LastRow + 1).Value = TextBox1.Text
    
ActiveSheet.Range(Cells(LastRow + 1, 2), Cells(LastRow + 1, 2 + NumAnsLines)).Value = _
Split(TextBox2.Text, Chr(10))
End Sub

to activate automatically for a sheet added to vba for say sheets1

Private Sub Worksheet_Activate()
UserForm1.Show 0
End Sub

or have a button only on the sheet with your lock row link to marco

Sub AddQF()
UserForm1.Show 0
End Sub

 

[Stormania94]

Also, what do I have to modify on your first code if I want that Answers text box values (e.g. TextBox2) to be inserted to column C or D ?

 

[Stormania94]

Never mind I have amended the line:

ActiveSheet.Range(Cells(LastRow + 1, 3), Cells(LastRow + 1, 3 + NumAnsLines)).Value = _
Split(TextBox2.Text, Chr(10))

Thanks a lot for your help. Userform is a better solution for me.