possible to lock down an employee timesheet

  • Thread starter Thread starter mikeg
  • Start date Start date
M

mikeg

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!
 
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,

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....
Click to expand...

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 ?
 
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
 
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!
Click to expand...



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
 
Gord,

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