Add date to rows only containing userform txtbox data

[Agates]

Creating form that sends textbox data to database with a timestamp for only each textbox that contains data. The code I have writes the date to all rows regardless if there is data or not.

Have tried a few things, but cannot seem to make it work right. I'm sure there is a simple solution, but I am not seeing it.

Basic Code:

Private Sub EnterPrint_cmd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SampleLog")
Dim ts As Date
ts = Now

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for Person
If Trim(Me.txtPerson.Value) = "" Then
Me.txtPerson.SetFocus
MsgBox "Please enter Person"
Exit Sub
End If

'check for a Sample Id
If Trim(Me.txtSample1.Value) = "" Then
Me.txtSample1.SetFocus
MsgBox "Please enter a Sample Id"
Exit Sub
End If

'copy the sample id to database
ws.Cells(iRow, 1).Value = Me.txtSample1.Value
ws.Cells(iRow + 1, 1).Value = Me.txtSample2.Value
ws.Cells(iRow + 2, 1).Value = Me.txtSample3.Value
ws.Cells(iRow + 3, 1).Value = Me.txtSample4.Value
ws.Cells(iRow + 4, 1).Value = Me.txtSample5.Value
ws.Cells(iRow + 5, 1).Value = Me.txtSample6.Value
ws.Cells(iRow + 6, 1).Value = Me.txtSample7.Value
ws.Cells(iRow + 7, 1).Value = Me.txtSample8.Value
ws.Cells(iRow + 8, 1).Value = Me.txtSample9.Value
ws.Cells(iRow + 9, 1).Value = Me.txtSample10.Value
ws.Cells(iRow + 10, 1).Value = Me.txtSample11.Value
ws.Cells(iRow + 11, 1).Value = Me.txtSample12.Value
ws.Cells(iRow + 12, 1).Value = Me.txtSample13.Value
ws.Cells(iRow + 13, 1).Value = Me.txtSample14.Value

'add date to database
ws.Cells(iRow, 2).Value = ts
ws.Cells(iRow + 1, 2).Value = ts
ws.Cells(iRow + 2, 2).Value = ts
ws.Cells(iRow + 3, 2).Value = ts
ws.Cells(iRow + 4, 2).Value = ts
ws.Cells(iRow + 5, 2).Value = ts
ws.Cells(iRow + 6, 2).Value = ts
ws.Cells(iRow + 7, 2).Value = ts
ws.Cells(iRow + 8, 2).Value = ts
ws.Cells(iRow + 9, 2).Value = ts
ws.Cells(iRow + 10, 2).Value = ts
ws.Cells(iRow + 11, 2).Value = ts
ws.Cells(iRow + 12, 2).Value = ts
ws.Cells(iRow + 13, 2).Value = ts

'clear the data
Me.txtSample1.Value = ""
Me.txtSample2.Value = ""
Me.txtSample3.Value = ""
Me.txtSample4.Value = ""
Me.txtSample5.Value = ""
Me.txtSample6.Value = ""
Me.txtSample7.Value = ""
Me.txtSample8.Value = ""
Me.txtSample9.Value = ""
Me.txtSample10.Value = ""
Me.txtSample11.Value = ""
Me.txtSample12.Value = ""
Me.txtSample13.Value = ""
Me.txtSample14.Value = ""
Me.txtPerson.Value = ""
Me.txtPerson.SetFocus
End Sub

 

[JLGWhiz]

I think this will work. You can give it a shot and post back if it throws an error. I didn't text it so it very well might. Just note the error message and the line highlighted when you click debug, if it does error.

Private Sub EnterPrint_cmd_Click()
Dim iRow As Long
Dim ws As Worksheet, txtSamp As Variant
Set ws = Worksheets("SampleLog")
Dim ts As Date
ts = Now
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for Person
If Trim(Me.txtPerson.Value) = "" Then
Me.txtPerson.SetFocus
MsgBox "Please enter Person"
Exit Sub
End If
'check for a Sample Id
If Trim(Me.txtSample1.Value) = "" Then
Me.txtSample1.SetFocus
MsgBox "Please enter a Sample Id"
Exit Sub
End If
'Create array for textboxes
txtSamp = Array(txtSample1, txtSample2, txtSample3, txtSample4, txtSample5, txtSample6, txtSample7, _
txtSample8, txtSample9, txtSample10, txtSample11, txtSample12, txtSample13, txtSample14)
For i = 0 To 13
If Me.txtSample.Value <> "" Then 'Test each tb for value
 ws.Cells(iRow + i, 1) = Me.txtSamp(i) 'Post value
End If
Next
For j = 0 To 13
If ws.Cells(iRow + j, 1) <> "" Then 'Test if value entered in range col A
 ws.Cells(iRow + j, 2) = ts 'Date/time stamp
End If
For k = 0 To 13 'Clean up
 Me.txtSamp(k).Value = ""
Next
Me.txtPerson,Value = ""
Me.txtPerson.SetFocus
End Sub

 

[Agates]

Very clean code, great improvement, but getting compile error "method or data member not found" at every reference to Me.txtSamp....

 

[JLGWhiz]

Very clean code, great improvement, but getting compile error "method or data member not found" at every reference to Me.txtSamp....

Try it with the typos removed.

Private Sub EnterPrint_cmd_Click()
Dim iRow As Long
Dim ws As Worksheet, txtSamp As Variant
Set ws = Worksheets("SampleLog")
Dim ts As Date
ts = Now
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for Person
    If Trim(Me.txtPerson.Value) = "" Then
        Me.txtPerson.SetFocus
        MsgBox "Please enter Person"
        Exit Sub
    End If
'check for a Sample Id
    If Trim(Me.txtSample1.Value) = "" Then
        Me.txtSample1.SetFocus
            MsgBox "Please enter a Sample Id"
            Exit Sub
    End If
'Create array for textboxes
txtSamp = Array(txtSample1, txtSample2, txtSample3, txtSample4, txtSample5, txtSample6, txtSample7, _
txtSample8, txtSample9, txtSample10, TxtSample11, txtSample12, txtSample13, txtSample14)
    For i = 0 To 13
        If txtSamp(i).Value <> "" Then 'Test each tb for value
            ws.Cells(iRow + i, 1) = txtSamp(i) 'Post value
        End If
    Next
    For j = 0 To 13
        If ws.Cells(iRow + j, 1) <> "" Then 'Test if value entered in range col A
        ws.Cells(iRow + j, 2) = ts 'Date/time stamp
        End If
    Next
    For k = 0 To 13 'Clean up
        txtSamp(k).Value = ""
    Next
txtPerson.Value = ""
txtPerson.SetFocus
End Sub

 

[Agates]

Thank you, works perfectly. Not sure I completely understand how the array thing works, very useful though, guess I should probably study up on it.