Solved: Adding Customers to my project

[jamieCR9]

Basically i have a school project, and on one of my sheets I have a button which brings up a Userform. On the userform, the user will enter their details and press a submit button, which is coded like this:
[VBA]Private Sub CommandButton1_Click()
Dim response
Dim LastRow As Object

Set LastRow = Sheet3.Range("a65536").End(xlUp)

LastRow.Offset(7, 1).Value = TextBox1.Text
LastRow.Offset(7, 2).Value = TextBox2.Text
LastRow.Offset(7, 3).Value = TextBox3.Text
LastRow.Offset(7, 4).Value = TextBox4.Text
LastRow.Offset(7, 5).Value = TextBox5.Text
LastRow.Offset(7, 6).Value = TextBox6.Text
MsgBox "Your details have been recorded."

response = MsgBox("Do you want to enter another record?", vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""


TextBox1.SetFocus

Else
Unload Me
End If
End Sub[/VBA]
What I want to achieve is when they enter their details, the focus goes onto the line below. However, when I try to record another set of customer data the new user's details just overwrite the old user's data. Can anyone help solve this issue for me? I've added a copy of my spreadsheet if anyone thinks it would help further explain.

[lucas]

This works:

[VBA]Set LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp)[/VBA]

[jamieCR9]

It's not exactly working with the co-ordinates I have used, instead of those co-ordinates the text is going down to row 100 or something? In fact, it's not working at all, it's just overwriting the text from the previous record.

[lucas]

I don't have 2007 so I had to move your sheet to another workbook.

If you are trying to add data to sheet customerID then this should work.

If it continues to put data far down on the sheet then you need to clear the contents of those cells at the bottom of the page before it will begin to work correctly.

It is looking for the last data in column B and then putting your entries in on the next row.

[VBA]Private Sub CommandButton1_Click()
Dim response
Dim LastRow As Object
Set LastRow = Sheet3.Range("B" & Rows.Count).End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
MsgBox "Your details have been recorded."
response = MsgBox("Do you want to enter another record?", vbYesNo)
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""

TextBox1.SetFocus
Else
Unload Me
End If
End Sub[/VBA]

[jamieCR9]

Perfect, you're a hero.

[lucas]

Be sure to mark your thread solved using the thread tools at the top of the page.

That way others won't read the entire thread just to find it's been solved.