Issues with time clock

[valiant]

I was hoping I could get some help on a time clock like function I had
created previously with some help from this site. As it currently stands, I
have a working time clock-like macro for use in an excel program. The
current code is:

Sub TimeStampStart()
Const TimeStampColumn As String = "D"
Const PWORD As String = ""
ActiveSheet.Unprotect PWORD
Dim X As Long
Dim LastUsedRow As Long
With ActiveSheet
LastUsedRow = .Cells(Rows.Count, TimeStampColumn).End(xlUp).Row
.Cells(LastUsedRow - (.Cells(LastUsedRow, TimeStampColumn). _
Value <> ""), TimeStampColumn).Value = Now
End With

Const DateStampColumn As String = "A"
Dim Y As Long
Dim LastUnusedRow As Long
With ActiveSheet
LastUnusedRow = .Cells(Rows.Count, DateStampColumn).End(xlUp).Row
If .Cells(LastUnusedRow, DateStampColumn).Value <> "" Then
LastUnusedRow = LastUnusedRow + 1
End If
.Cells(LastUnusedRow, DateStampColumn).Value = Date
.Cells(LastUnusedRow, TimeStampColumn).Value = Time
End With
ActiveSheet.Protect PWORD
ActiveWorkbook.Save

End Sub

The current issue I am having is that a potential employee could clock into
a new job (if they changed to a different project) and forget to clock out of
their old job. I am wondering if there is any way that when an employee runs
the above macro, it could check to see if the previous row had an entry
filled in for column F, the time out column. Any help would be appreciated,
thank you.

P.S. I posted a similar message on Tuesday and hadn't received a response
so I thought I should post again because it may have been missed. I
apologize if this is improper etiquette.

 

[john]

not sure I have understood you but had a play with your code - see if it helps!

Sub TimeStampStart()
Const TimeStampColumn As String = "D"
Const DateStampColumn As String = "A"
Const ClockOffColumn As String = "F"
Const PWORD As String = ""

Dim Y As Long
Dim LastUnusedRow As Long
Dim X As Long
Dim LastUsedRow As Long

MyTitle = "Start Project"

With ActiveSheet
.Unprotect PWORD

LastUsedRow = .Cells(Rows.Count, TimeStampColumn).End(xlUp).Row

If LastUsedRow > 1 Then
If .Cells(LastUsedRow - 1, ClockOffColumn).Value <> "" Then

.Cells(LastUsedRow - (.Cells(LastUsedRow, TimeStampColumn). _
Value <> ""), TimeStampColumn).Value =
Now

LastUnusedRow = .Cells(Rows.Count,
DateStampColumn).End(xlUp).Row
If .Cells(LastUnusedRow, DateStampColumn).Value <> "" Then
LastUnusedRow = LastUnusedRow + 1
End If
.Cells(LastUnusedRow, DateStampColumn).Value = Date
.Cells(LastUnusedRow, TimeStampColumn).Value = Time
Else
msg = MsgBox("You Must Clock Off Before Starting" & _
"Another Project", 16, MyTitle)
End If
End If
.Protect PWORD
End With

ActiveWorkbook.Save

End Sub

 

[valiant]

That's actually even more than I wanted. It works nicely except one problem.
Currently the top row of my spreadsheet consists of column headers. Because
of this (I think) the macro doesn't work when none of the rows have data
except the top row. I believe this is because the top row contains
characters instead of numbers. What do you think?

 

[Bob Phillips]

Sub TimeStampStart()
Const TimeStampColumn As String = "D"
Const PWORD As String = ""
ActiveSheet.Unprotect PWORD
Dim X As Long
Dim LastUsedRow As Long
With ActiveSheet
LastUsedRow = .Cells(Rows.Count, TimeStampColumn).End(xlUp).Row
.Cells(LastUsedRow - (.Cells(LastUsedRow, TimeStampColumn). _
Value <> ""), TimeStampColumn).Value = Now
End With

Const DateStampColumn As String = "A"
Dim Y As Long
Dim LastUnusedRow As Long
With ActiveSheet
LastUnusedRow = .Cells(Rows.Count, DateStampColumn).End(xlUp).Row
If .Cells(LastUnusedRow, DateStampColumn).Value <> "" Then
LastUnusedRow = LastUnusedRow + 1
End If
If .Cells(LastUnusedRow - 1, TimeStampColumn).Value = "" Then
MsgBox "previous entry not clocked out"
Else
.Cells(LastUnusedRow, DateStampColumn).Value = Date
.Cells(LastUnusedRow, TimeStampColumn).Value = Time
End If
End With
'ActiveSheet.Protect PWORD
ActiveWorkbook.Save

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[john]

I guessed that you may have headers which is why the test If LastUsedRow > 1
was added but I don't think rest of my logic construct is that good.
As a simple test and assuming you have data in row 2 change:
LastUsedRow > 1 to LastUsedRow > 2 crude but hopefully, will work!

 

[valiant]

Thanks Bob and John, both of your advices helped a lot and I now have it
working the way I wanted it to. I appreciate the help.