Time Stamp

[sm8392]

Hello everyone!

I'm currently working on a Macro-enabled Timesheet for my job where the employees can click a button to record the time they come in to work and leave.

I have 4 fields that require a timestamp per day (Clock in for the day, clock out for lunch, clock in from lunch, and clock out for the day).

I also have two additional buttons that are used to mark a particular day as a holiday or vacation.

I'm trying to protect the worksheet so that the employees cannot alter or delete an entry so I unchecked the option to allow users to select locked/unlocked cells, but when I do so it does not allow the user to select the cell where they will have the timestamp recorded (Example below).

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Time In[/TD]
[TD]Time Out (Lunch)[/TD]
[TD]Time In (Lunch)[/TD]
[TD]Time Out[/TD]
[/TR]
[TR]
[TD]06/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06/09/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is the code that I have for the Clock In/Out button:

Sub EnterTime()
ActiveSheet.Unprotect Password:="password"
Dim DT
'
' MyTimeStamp Macro
'
' Keyboard Shortcut: Ctrl+t
'


DT = Format(Now, "hh:mm:ss AM/PM")
ActiveCell.Select
Selection.NumberFormat = "hh:mm AM/PM"
ActiveCell.Value = DT
ActiveSheet.Protect Password:="password", _
DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

The issue that I am having is that it is not entering the timestamp in the cells that it needs to be entered in. The only way I have been able to make it work is when the sheet is not protected and the user can select the cell where the timestamp needs to be entered, but in doing this the user can still delete the entry.

Does anyone know how I can get this to work?

 

[mumps]

Make sure that all the cells which will contain the time stamp are unlocked. Then protect the worksheet, select a cell and run this macro:

Sub EnterTime()
    ActiveSheet.Unprotect Password:="password"
    Dim DT
    DT = Format(Now, "hh:mm:ss AM/PM")
    ActiveCell.Select
    Selection.NumberFormat = "hh:mm AM/PM"
    ActiveCell.Value = DT
    ActiveCell.Locked = True
    ActiveSheet.Protect Password:="password", _
    DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[mumps]

This is another approach that might work better for you. Assuming that your time stamps will go in columns B to E, unlock all the cells in those columns. Protect the worksheet with the word "password". When you protect the sheet, unclick "Select Locked Cells". Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. You can change the column target range to match the four columns that will contain the time stamp if necessary. Close the code window to return to your sheet. Double click in any cell where you want a time stamp.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("B:E")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="password"
    Target = Format(Now, "hh:mm:ss AM/PM")
    Target.Locked = True
    ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[sm8392]

Thank you!

This works for the first cell, but when I click the button to "Clock Out" it doesn't move to the next cell, it just updates the same cell (in this case B6). Any tips on how to fix this?

 

[mumps]

Have you tried my second suggestion in Post #3 . I think this will make it much easier for you.

 

[sm8392]

Never mind, it's not working

I added the code you suggested in Post #3 and linked it to the button but it's not working. I get an error message: Argument not optional.

 

[mumps]

The macro I suggested will trigger automatically when you double click the cell. You don't need the button. Please follow the instructions in Post #3 .

 

[sm8392]

Ok, I followed the instructions. Now it is requesting the password, so I entered it and it enables the cell to be edited. However, the purpose of this is to prevent the employees from editing the cells, we only want them to be able to record the time they clock in and out with the click of a button.

 

[mumps]

Did you unlock the cells which will hold the time stamp, in this case columns B to E? If they are unlocked, you will not be asked for a password. When you protect a sheet you do not have access to any cells that are locked. If you unlock them, you do have access even though the sheet is protected. The way the macro works is that it will be triggered automatically when you double click any cell in columns B to E. When you double click the cell, the time stamp will be entered automatically and the cell will be locked so that it can't be modified. Please follow the instructions carefully otherwise the macro won't work. I tried it on a dummy sheet and it worked perfectly.

 

[sm8392]

You were absolutely right, it does work! I'm sorry for all of the back and forth, it is working perfectly now.

Thank you for all your help.

One more question, do you happen to know if there is any code that can be added to record the computer's IP address or name?