Question about getting a form to enter data into the next empty line on a sheet?

[archangel1177]

I have a project that I am working on and I have the form built for entering data. What I have right now is code that will enter the data onto the first line of the sheet. But it will only out the data in the first row and never advances. Here is what I have.

Private Sub CommandButton1_Click()
RowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet1").Range("A2").Value = UserForm1.TextBox1.Value
Sheets("Sheet1").Range("B2").Value = UserForm1.TextBox2.Value
Sheets("Sheet1").Range("C2").Value = UserForm1.TextBox3.Value
Sheets("Sheet1").Range("D2").Value = UserForm1.TextBox4.Value
Sheets("Sheet1").Range("E2").Value = UserForm1.TextBox5.Value
Sheets("Sheet1").Range("F2").Value = UserForm1.TextBox6.Value
Sheets("Sheet1").Range("G2").Value = UserForm1.TextBox7.Value
Sheets("Sheet1").Range("H2").Value = UserForm1.TextBox8.Value
Sheets("Sheet1").Range("I2").Value = UserForm1.TextBox10.Value
Sheets("Sheet1").Range("J2").Value = UserForm1.TextBox9.Value

End Sub

Also if someone could point me in the right direction on how to have the user form clear after the submit button is clicked that would also be greatly appreciated. Here is a link to where I have the actual worksheet. Thanks in advance!https://www.dropbox.com/s/8l3cftajhideq7w/Order tracker prototype(2).xls

 

[rpaulson]

Change
RowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
to
RowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row +1


Change to
Sheets("Sheet1").Cells(RowCount,"A").Value = UserForm1.TextBox1.Value
Sheets("Sheet1").Cells(RowCount,"B").Value = UserForm1.TextBox2.Value
...


To Clear old inputs try
UserForm1.TextBox1.Value =""

 

[bstory84]

Here is a very manual way to do it.

Private Sub CommandButton1_Click()
Dim myRow As Long
 myRow = Sheets("sheet1").Range("A9999").End(xlUp).Row + 1
Sheets("Sheet1").Cells(myRow, 1).Value = UserForm1.TextBox1.Value
Sheets("Sheet1").Cells(myRow, 2).Value = UserForm1.TextBox2.Value
Sheets("Sheet1").Cells(myRow, 3).Value = UserForm1.TextBox3.Value
Sheets("Sheet1").Cells(myRow, 4).Value = UserForm1.TextBox4.Value
Sheets("Sheet1").Cells(myRow, 5).Value = UserForm1.TextBox5.Value
Sheets("Sheet1").Cells(myRow, 6).Value = UserForm1.TextBox6.Value
Sheets("Sheet1").Cells(myRow, 7).Value = UserForm1.TextBox7.Value
Sheets("Sheet1").Cells(myRow, 8).Value = UserForm1.TextBox8.Value
Sheets("Sheet1").Cells(myRow, 9).Value = UserForm1.TextBox10.Value
Sheets("Sheet1").Cells(myRow, 10).Value = UserForm1.TextBox9.Value
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.TextBox5.Value = ""
UserForm1.TextBox6.Value = ""
UserForm1.TextBox7.Value = ""
UserForm1.TextBox8.Value = ""
UserForm1.TextBox10.Value = ""
UserForm1.TextBox9.Value = ""
End Sub

 

[dmt32]

this may do what you want:

Private Sub CommandButton1_Click()
    Dim RowCount As Long
    Dim c As Integer
    Dim bx As Integer
    With Sheets("Sheet1")
        RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        For c = 1 To 10
            bx = c
            If c = 9 Then bx = 10
            If c = 10 Then bx = 9
            .Cells(RowCount, c).Value = Me.Controls("TextBox" & bx).Value
            Me.Controls("TextBox" & bx).Value = ""
        Next c
    End With
End Sub

Dave

 

[archangel1177]

Thank all of you for the information! I will give them a try here shortly and I will post the results!