Userform to worksheet problem

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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 :

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

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

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

HTH

DominicB
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,188
Members
417,259
Latest member
gtacw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top