PDA

View Full Version : Solved: Adding Customers to my project



jamieCR9
02-21-2010, 08:54 AM
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:
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
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
02-21-2010, 09:05 AM
This works:

Set LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp)

jamieCR9
02-21-2010, 09:15 AM
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
02-21-2010, 09:32 AM
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.

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

jamieCR9
02-21-2010, 09:37 AM
Perfect, you're a hero. :)

lucas
02-21-2010, 09:46 AM
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.