Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with time clock
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with time clock
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 -- jb "valiant" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with time clock
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? "john" wrote: 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 -- jb "valiant" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with time clock
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) "valiant" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with time clock
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! -- jb "valiant" wrote: 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? "john" wrote: 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 -- jb "valiant" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with time clock
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. "Bob Phillips" wrote: 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) "valiant" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incremental time values based upon clock in and clock out times | Excel Discussion (Misc queries) | |||
Time Clock - Making sure they clock out | Excel Programming | |||
How do I calculate time in excel (clock in and clock out | Excel Discussion (Misc queries) | |||
Change EXCEL Clock to Standard Clock or Military Time | Excel Worksheet Functions | |||
can cell's act like time clock at a certen time/date for payments | Excel Discussion (Misc queries) |