Capture Data Entered in textboxes into New Worksheet

[ellehcer08]

I have a userform with 4 textbox in it, and I want the information typed in those textbox to be saved in a NEW sheet on the same workbook.

For example, if I type information in those textbox, and I click the button SAVE, it will generate a new sheet and save the information there in cell B2, B3, B5 and B7 respectively. (New worksheet everytime I click SAVE).

Please help! Thank you.

 

[tlowry]

In the form design:

1. Right click the SAVE button
2. Insert this code:
3. Test

 With ThisWorkbook.Sheets.Add
        .Range("B2") = Me.TextBox1
        .Range("B3") = Me.TextBox2
        .Range("B5") = Me.TextBox3
        .Range("B7") = Me.TextBox4
    End With

 

[ellehcer08]

Hi! Thanks for the code.. It seems to work alone, but if I added my code it doesn't seem to work for me.

Private Sub CommandButton1_Click()

' to rename added sheet to textbox1's information

Sheets.Add.Name = TextBox1
ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
       
       
End Sub

And btw, what I want is to find the next empty row (or atleast with a single line empty row -- Kinda like a recipe book outlook).

 

[tlowry]

Try:

Private Sub CommandButton1_Click()
    With ThisWorkbook.Sheets.Add(after:=Sheets(ThisWorkbook.Sheets.Count))
        .Name = "TextBox1"
        .Range("B2") = Me.TextBox1
        .Range("B3") = Me.TextBox2
        .Range("B5") = Me.TextBox3
        .Range("B7") = Me.TextBox4
    End With
End Sub

And btw, what I want is to find the next empty row (or atleast with a single line empty row -- Kinda like a recipe book outlook).

Not sure what you want here. Can you give an example?

 

[tlowry]

woops! take the quotes off of "TextBox1"

.Name = TextBox1

 

[ellehcer08]

the code works great! thanks a lot..
though I have things I need to do with this still, and try to figure out first...but if I can't do it on my own, I'll just ask again
Thank you so much!!

 

[ellehcer08]

the code works great! thanks a lot..

this is my userform example:

textbox --- Recipe Title - txtRecipeName ----must be posted to cell B2
Source - txtSource ---- must be posted to cell B3
Ingredients - txtIngredients (multilines) ------must be posted to cell B6
Procedure - txtProcedure (multilines)

what I want in the "Procedure" is to be posted to the next 2 empty lines next to "Ingredients". The txtIngredients has multilines so it will be indefinite how many lines it can take up in the sheet.

Please see the image, it's kinda like this:

 

[tlowry]

Does this do what you want?

Private Sub CommandButton1_Click()
    With ThisWorkbook.Sheets.Add(after:=Sheets(ThisWorkbook.Sheets.Count))
        .Name = TextBox1
        .Range("B2") = Me.TextBox1
        .Range("B3") = Me.TextBox2
        .Range("B6") = "Ingredients:"
        linesTB3 = Split(Me.TextBox3.Text, vbCrLf)
        For i = 0 To UBound(linesTB3)
            .Range("B7").Offset(i, 0) = linesTB3(i)
        Next i
        linesTB4 = Split(Me.TextBox4.Text, vbCrLf)
        startrow = 10 + UBound(linesTB3)
        .Range("B" & startrow) = "Procedure:"
        startrow = startrow + 1
        For i = 0 To UBound(linesTB4)
            .Range("B" & (i + startrow)) = linesTB4(i)
        Next i
    End With
End Sub

 

[ellehcer08]

hi tlowry! thanks a lot.. works great!!!

just one thing! i said that txtIngredients (or TextBox3) and txtProcedure (or TextBox4) has both MULTILINES...
so when I add the information in it.. it comes in just one line (both of them). can I make it appear on multilines as well in the sheet?

here's what happened:

 

[tlowry]

With this last code, the multi-line text boxes should be in multiple should be in the worksheet.

How are you putting the data in these boxes on the form? The code is looking for a CRLF to indicate new line. Did UNIX get in here?

Excel 2000

	A	B	C	D
Sample Name
Sample Source
Ingredients:
Sample ingredient 1
Sample ingredient 2
Sample ingredient 3
Sample ingredient 4
Procedure:
Sample procedure 1
Sample procedure 2
Sample procedure 3

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sample Name​