| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel form. Need data extracted to spreadsheet each time a form co | Excel Discussion (Misc queries) | |||
| adding rows, pasting values then empty sheet | Excel Worksheet Functions | |||
| How do i delete empty rows in a spreadsheet | Excel Discussion (Misc queries) | |||
| What produces empty rows at the bottom of a spreadsheet? | Excel Discussion (Misc queries) | |||
| Adding items to a spreadsheet from a user form listbox | Excel Programming | |||
Linear Mode
