ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Adding data from Form to empty rows in spreadsheet. (https://www.excelbanter.com/excel-programming/381054-adding-data-form-empty-rows-spreadsheet.html)

dd
Adding data from Form to empty rows in spreadsheet.
 
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



dd
Adding data from Form to empty rows in spreadsheet.
 
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

dd
Adding data from Form to empty rows in spreadsheet.
 
Thanks Tom,

I got the answer e.g
.Cells(lRow, 4).Value = FormatDateTime(Me.txtDateRec.Value, 1)

Regards
Dylan

"dd" <dd.dd wrote in message ...
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


All times are GMT +1. The time now is 01:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com