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.
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.