possible to lock down an employee timesheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mikeg - 05 Nov 2007 22:29 GMT

Hi all,

Thank you for reading my post:

My goal: create a locked down timecard spreadsheet for each employee

Is it possible so that each time an employee enters a "time in" or "time
out"; each respective field is then locked for editing?
For example, an employee goes to his or her private folder on a file server
and types in the time he/she came in to work that morning. After typing in
that time and hitting enter, can that field be then locked so the employee
can't go back and try to edit it? Let me know if this can be done....

thanks!

Reply to this Message

Bruce Sinclair - 06 Nov 2007 00:34 GMT

>Hi all,
>
[quoted text clipped - 8 lines]
>that time and hitting enter, can that field be then locked so the employee
>can't go back and try to edit it? Let me know if this can be done....

I doubt it ... but I also wonder why you would want to do this ? What if
they type in the time wrongly (for an obvious start :) ) ? ... what if they
lie ? :)

What do you gain by locking the input cells ?

Reply to this Message

mikeg - 06 Nov 2007 00:12 GMT

All good questions Bruce, I'm just trying to define ways in order to prevent
workers from cheating their time. Think of it this way: I'm trying to create
a software based version of an analog timeclock where you take your paper
timecard and punch it. If the employee mis-punched, then he/she would need
to present a valid excuse to HR. We don't have a budget for a fancy
timeclock system. Thanks for responding to my post.

>>Hi all,
>>
[quoted text clipped - 17 lines]
>
> What do you gain by locking the input cells ?

Reply to this Message

Gord Dibben - 06 Nov 2007 01:28 GMT

Mike

It is possible to lock down a cell after something has been entered into it.

This event code will lock each cell in column A as something is entered in that
cell.

Assumes you have unlocked all cells and protected the sheet under
Tools>Protection>Protect Sheet.

Note: no error-checking to make sure user enters a valid time.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
ActiveSheet.Unprotect Password:="justme"
n = Target.Row
If Target.Value <> "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

I suppose you would want to stop prying eyes from seeing the password so you
will also have to protect the code from those eyes.

Alt + F11 to open Visual Basic Editor.

Select your workbook/project and right-click>VBAProject
Properties>Protection>Lock for Viewing. Apply a password and save/close the
workbook.

When re-opened, code will be unviewable or uneditable.

Gord Dibben MS Excel MVP

>All good questions Bruce, I'm just trying to define ways in order to prevent
>workers from cheating their time. Think of it this way: I'm trying to create
[quoted text clipped - 24 lines]
>>
>> What do you gain by locking the input cells ?

Reply to this Message

mikeg - 07 Nov 2007 00:08 GMT

Gord,

Thanks for the response! I will apply this code and let you know. I really
appreciate your help!
regards,
Mike

> Mike
>
[quoted text clipped - 76 lines]
>>>
>>> What do you gain by locking the input cells ?

Reply to this Message

GuyFromPerth@gmail.com - 06 Nov 2007 07:56 GMT

> Hi all,
>
[quoted text clipped - 10 lines]
>
> thanks!

Mike,

Another option would be to use the Now() function in a cell at the
top of an employees sheet and protect the sheet with a password

You could then set up a button with a short piece of code attached
that would do the following:

Unprotect the sheet
Insert a row iat the top of a table somewhere below
Copy the time and paste it into that new row
Protect the sheet

This way you have a running record of the time when the employess has
clicked the button (ie at the start and end of the day)

e.g

If you have =Now() in cell A1

Then enter the following code into a Module or ThisWorkbook via the
VBEditor (Alt+F11)

Sub TimeCard()

ActiveSheet.Unprotect "secretword"
Calculate
Range("A5").Select
Selection.EntireRow.Insert
Range("A1").Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats
ActiveSheet.Protect "secretword"

End Sub

Then add a macro button to the worksheet near the time in A1 and
assign this macro to it. All the employee has to do is open the file
and click on the button at the start and end of the day.

Probably worth adding an immediate autosave also incase the employee
forgets to save it

Should be good to go then

As suggested by Gord you will also need to protect access to the
password in the VB Editor

Hope that helps

Reply to this Message