Data from a userform transferring to the same row in Excel.

[VIPERMAN1]

HI, i have so far put together the below code for an "OK" command button

When i do click OK, the data is transferred to the same cell (A75) as the last entry, but i would like it to go in the next row available, in this case A76.

Am i supposed to be looking at an Offset rule in the part of the code that writes the data to worksheet (which i have put in bold below).

Any guidance would be appreciated.

Private Sub cmdOK_Click()


Dim RowCount As Long
Dim ctl As Control
'Check user input
If Me.TxttFirstName.Value = "" Then
MsgBox "Please enter a First Name.", vbExclamation, "Season Ticket"
Me.TxttFirstName.SetFocus
Exit Sub
End If

If Me.TxttSurname.Value = "" Then
MsgBox "Please enter a Surname.", vbExclamation, "Season Ticket"
Me.TxttSurname.SetFocus
Exit Sub
End If

If Me.TxttDoB.Value = "" Then
MsgBox "Please enter a Date Of Birth.", vbExclamation, "Season Ticket"
Me.TxttDoB.SetFocus
Exit Sub
End If

If Me.TxttAddress1.Value = "" Then
MsgBox "Please enter an Address.", vbExclamation, "Season Ticket"
Me.TxttAddress1.SetFocus
Exit Sub
End If

If Me.TxttTown.Value = "" Then
MsgBox "Please enter a Town.", vbExclamation, "Season Ticket"
Me.TxttTown.SetFocus
Exit Sub
End If

If Me.TxttCountry.Value = "" Then
MsgBox "Please enter a Country.", vbExclamation, "Season Ticket"
Me.TxttCountry.SetFocus
Exit Sub
End If

If Me.txttPostcode.Value = "" Then
MsgBox "Please enter a Postcode.", vbExclamation, "Season Ticket"
Me.txttPostcode.SetFocus
Exit Sub
End If

If Me.TxttEmail.Value = "" Then
MsgBox "Please enter an Email Address.", vbExclamation, "Season Ticket"
Me.TxttEmail.SetFocus
Exit Sub
End If

If Me.txttTelephone.Value = "" Then
MsgBox "Please enter a Telephone Number.", vbExclamation, "Season Ticket"
Me.txttTelephone.SetFocus
Exit Sub
End If

If Me.CmbooTicketType.Value = "" Then
MsgBox "Please select a Ticket Type.", vbExclamation, "Season Ticket"
Me.CmbooTicketType.SetFocus
Exit Sub
End If

If Me.TxttDateOfApplication.Value = "" Then
MsgBox "Please enter the date the application was received.", vbExclamation, "Season Ticket"
Me.TxttDateOfApplication.<wbr>SetFocus
Exit Sub
End If

If Me.CmbooRequestedSeat.Value = "" Then
MsgBox "Please select a Seat.", vbExclamation, "Season Ticket"
Me.CmbooRequestedSeat.<wbr>SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txttTelephone.<wbr>Value) Then
MsgBox "The Telephone box must contain a number.", vbExclamation, "Season Ticket"
Me.txttTelephone.SetFocus
Exit Sub
End If


If Not IsDate(Me.TxttDoB.Value) Then
MsgBox "The Date Of Birth box must contain a date.", vbExclamation, "Season Ticket"
Me.TxttDoB.SetFocus
Exit Sub
End If


If Not IsDate(Me.<wbr>TxttDateOfApplication.Value) Then
MsgBox "The Date Of Application Received box must contain a date.", vbExclamation, "Season Ticket"
Me.TxttDateOfApplication.<wbr>SetFocus
Exit Sub
End If
'Write data to worksheet
RowCount = Worksheets("Ticket Holder Details").Range("A75").<wbr>CurrentRegion.Rows.Count
With Worksheets("Ticket Holder Details").Range("A75")

.Offset(RowCount, 3).Value = Me.TxttFirstName.Value
.Offset(RowCount, 4).Value = Me.TxttSurname.Value
.Offset(RowCount, 5).Value = DateValue(Me.TxttDoB.Value)
.Offset(RowCount, 6).Value = Me.TxttAddress1.Value
.Offset(RowCount, 7).Value = Me.TxttAddress2.Value
.Offset(RowCount, 8).Value = Me.TxttTown.Value
.Offset(RowCount, 9).Value = Me.TxttCountry.Value
.Offset(RowCount, 10).Value = Me.txttPostcode.Value
.Offset(RowCount, 11).Value = Me.TxttEmail.Value
.Offset(RowCount, 12).Value = Me.txttTelephone.Value
.Offset(RowCount, 13).Value = Me.CmbooTicketType.Value
.Offset(RowCount, 14).Value = DateValue(Me.<wbr>TxttDateOfApplication.Value)
.Offset(RowCount, 17).Value = Me.CmbooRequestedSeat.Value

If Me.ChkkAgeCheck.Value = True Then
.Offset(RowCount, 19).Value = "Yes"
Else
.Offset(RowCount, 19).Value = "No"
End If
.Offset(RowCount, 20).Value = Format(Now, "dd/mm/yyyy")

End With




' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

 

[Scott T]

When I run this the data is going 75 rows down from A75 because you are starting from cell A75 for your offset.

[FONT=arial]RowCount = Worksheets("Ticket Holder Details").Range("A75").<wbr>CurrentRegion.Rows.Count[/FONT]
[FONT=arial]With Worksheets("Ticket Holder Details").Range("A75")[/FONT]

[FONT=arial] .Offset(RowCount, 3).Value = Me.TxttFirstName.Value[/FONT]

Assuming that there is data in the sheet so that the excel gets the current region right then this would work but not sure why you would use A75 to get the current region.

RowCount = Worksheets("[FONT=Arial]Ticket Holder Details[/FONT]").Range("A75").CurrentRegion.Rows.Count
With Worksheets("[FONT=Arial]Ticket Holder Details[/FONT]").Range("A2")
 [FONT=Arial].Offset(RowCount, 3).Value = Me.TxttFirstName.Value[/FONT]
End With