Thanks Tom,
One more question regarding this UserForm
The dates appear in dd/mm/yyyy format in the form but are input as
mm/dd/yyyy format on the spreadsheet.
How do I get them to input as dd/mm/yyyy format?
Regards
Dylan
"Tom Ogilvy" wrote in message
...
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
should be
lRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
since you don't write any data into column 1 you should check for the last
used row in column 2 where you do write data.
--
Regards,
Tom Ogilvy
"dd" wrote:
I'm trying to add data to the last empty row in my worksheet. Using an
example from contextures which has...
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
However, my form adds data to the first empty row then when I try to add
another entry it over writes this entry.
How do I get it to write to the next row and so on?
This is what I'm using (from
http://www.contextures.com/xlUserForm01.html):
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lSite As Long
Dim lElement As Long
Dim ws As Worksheet
Set ws = Worksheets("tblConditionDetails-Unlinked-19")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
lSite = Me.cboSite.ListIndex
lElement = Me.cboElement.ListIndex
'check for a Site
If Trim(Me.cboSite.Value) = "" Then
Me.cboSite.SetFocus
MsgBox "Please select a Site"
Exit Sub
End If
'check for a Element
If Trim(Me.cboElement.Value) = "" Then
Me.cboElement.SetFocus
MsgBox "Please select an Element"
Exit Sub
End If
'copy the data to the database
With ws
.Cells(lRow, 2).Value = Me.cboSite.Value
.Cells(lRow, 3).Value = Me.cboElement.Value
.Cells(lRow, 4).Value = Me.txtDateRec.Value
.Cells(lRow, 5).Value = Me.txtDescrip.Value
.Cells(lRow, 6).Value = Me.chkPhoto.Value
.Cells(lRow, 7).Value = Me.chkReport.Value
.Cells(lRow, 8).Value = Me.txtDateReport.Value
.Cells(lRow, 9).Value = Me.txtComments.Value
.Cells(lRow, 10).Value = Me.chkRemedy.Value
.Cells(lRow, 11).Value = Me.txtDateRemedy.Value
End With
'clear the data
Me.cboSite.Value = ""
Me.cboElement.Value = ""
Me.txtDateRec.Value = Format(Date, "Short Date")
Me.txtDescrip.Value = ""
Me.chkPhoto.Value = False
Me.chkReport.Value = False
Me.txtDateReport.Value = Format(Date, "Short Date")
Me.txtComments.Value = ""
Me.chkRemedy.Value = False
Me.txtDateRemedy.Value = Format(Date, "Short Date")
End Sub
Regards
D Dawson