Userform to worksheet problem

[litestream]

I have a Userform with 12 text boxes. When the form is complete. there is a button to send the data to the next available row on my worksheet.

However, there is a complication:
Text box1 data needs to be put into ColumnA
Text box2 data needs to be put into ColumnB
Text box3 data needs to be put into ColumnC
Text box4 data needs to be put into ColumnD
Text box5 data needs to be put into ColumnE
Text box6 data needs to be put into ColumnF
Text box7 data needs to be put into ColumnG
Text box8 data needs to be put into ColumnH
Text box9 data needs to be put into ColumnI
Text box10 data needs to be put into ColumnJ
Text box11 data needs to be put into ColumnL
Text box12 data needs to be put into ColumnM

Column K is printed onto my paper copy but does not contain data

All of the data from the 12 boxes needs to appear on the same row. When the command button is pressed to send the data, I need the form to be cleared ready for the next data to be entered.

Also, I have noticed that when I send my data from the Userform, it has a small green triangle in each cell to say that numbers are stored as text. How can I prevent this?

Would it also be possible to send the Userform data to 2 different worksheets once the button is clicked?

Any assistance would be greatly appreciated.

Private Sub CommandButton1_Click()

With Sheets("Sheet1").Columns(1)
.Cells(65536).End(xlUp).Offset(1) = TextBox1
End With

With Sheets("Sheet1").Columns(2)
.Cells(65536).End(xlUp).Offset(1) = TextBox2
End With

With Sheets("Sheet1").Columns(3)
.Cells(65536).End(xlUp).Offset(1) = TextBox3
End With

With Sheets("Sheet1").Columns(4)
.Cells(65536).End(xlUp).Offset(1) = TextBox4
End With

With Sheets("Sheet1").Columns(5)
.Cells(65536).End(xlUp).Offset(1) = TextBox5
End With

With Sheets("Sheet1").Columns(6)
.Cells(65536).End(xlUp).Offset(1) = TextBox6
End With

With Sheets("Sheet1").Columns(7)
.Cells(65536).End(xlUp).Offset(1) = TextBox7
End With

With Sheets("Sheet1").Columns(8)
.Cells(65536).End(xlUp).Offset(1) = TextBox8
End With

With Sheets("Sheet1").Columns(9)
.Cells(65536).End(xlUp).Offset(1) = TextBox9
End With

With Sheets("Sheet1").Columns(10)
.Cells(65536).End(xlUp).Offset(1) = TextBox10
End With

With Sheets("Sheet1").Columns(11)
.Cells(65536).End(xlUp).Offset(1) = TextBox11
End With

With Sheets("Sheet1").Columns(12)
.Cells(65536).End(xlUp).Offset(1) = TextBox12
End With

End Sub

 

[DominicB]

Good afternoon litestream

What you need to do is decide which column is to be the definative column which defines at which row the spreadsheet ends, then assign it to a variable. I'll assume column A :

n = Sheets("Sheet1").Range("A65536").End(xlUp).Row

Now all you need is one line per textbox to enter the values :

Cells(n + 1, 1).Value = TextBox1.Value   'Column A
Cells(n + 1, 2).Value = TextBox2.Value   'Column B etc

HTH

DominicB

 

[litestream]

Thanks for your prompt response