Timestamp

[brummy]

Hi

my boss has asked me to write an excel sheet that will timestamp workers log in times

workers are to use a password known only to themselves

password box will be blanked to hide password but once person logs on with correct password then it time stamps in next box

can anyone help me please

for example
name password
sharon 123
paul 987
fred 666

thank you

 

[brummy]

Thank you for replying

what if there were 12 employees, each with their own value
ie
Kevin = 123
sharon = 986
lisa = 492
etc, how would i employ that into the code?

 

[brummy]

brummy,

The below code will do what you've asked for in post#9 above:

Sub TimeStamp()
    If Range("C2").Value = "123" Then
        Range("B2").FormulaR1C1 = "Kevin"
        Range("D2").FormulaR1C1 = Time
        Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
    End If
End Sub

as you can see in post #3 i need to have a list of 12 staff each with their own 3 number code to correspond with their name ie
kevin=123
sharon=873
lisa=206

etc
i spotted this code below that does something similar to what i require but doesnt ask for 'c' to equal anything i particular

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Columns("C")) Is Nothing Then
If Target.Rows.Count > 1 Then
Dim Cell As Range
For Each Cell In Target
Cell.Offset(0, 1) = Now
Next Cell
Else
Cells(Target.Row, "E") = Now
End If
End If
End Sub

 

[brummy]

Never mind have solved it without using VBA

used VLOOKUP function alongside the NOW function

thanks anyway

 

[Jaafar Tribak]

Hi brummy.

Long time ago, I developped a similar clockIn time-sheet at work. I made it as foolproof and as user friendly as possible but obviously it is not perfect and needs some getting used to.

I have adapted it so hopefully it works for you too.

Workbook example.


Assumptions:

* Staff : 6 Managers and 6 Cashiers

Name Password
Manager 1 Manager 1
Manager 2 Manager 2
Manager 3 Manager 3
Manager 4 Manager 4
Manager 5 Manager 5
Manager 6 Manager 6
Cashier 1 Cashier 1
Cashier 2 Cashier 2
Cashier 3 Cashier 3
Cashier 4 Cashier 4
Cashier 5 Cashier 5
Cashier 6 Cashier 6

Obviously, you need to adapt the code/Worksheet with the real staff names and their respective passwords.

This is how it basically works :

The ClockIn weekly timesheet updates itself every day and highlights in yellow the column corresponding to the current day of the week. This is the only column that accepts input.

In order to enter a time stamp, you need the doubleclick the corresponding cell . Once the cell is doubleclicked, you are prompted for the corresponding staff Password. Once the cell is time stamped, it becomes locked.

If you have any problems when incorporating the real staff names and passwords to the workbook example you can PM me with the details and see if I can help.

 

[brummy]

Thank you very much

it is exactly what i require

much appreciated

 

[cedricthecat]

Bear in mind that the cool example as supplied by Jaafar above, does not seem to password protect the sheet so you may have to code this in to prevent overwrites (apologies if I have got this wrong - only looked at it briefly)

Also you might want to password protect the VBA as otherwise it's pretty easy to see the passwords!

 

[Jaafar Tribak]

Bear in mind that the cool example as supplied by Jaafar above, does not seem to password protect the sheet so you may have to code this in to prevent overwrites (apologies if I have got this wrong - only looked at it briefly)

Also you might want to password protect the VBA as otherwise it's pretty easy to see the passwords!

Hi Dave.

I didn't password protect the worksheet or the VBA Project on purpose.

Protection passwords should be added in the end after the code has been edited & revised and the user is happy with its final design.

Also, one important functionality that could be added to the workbook example is adding a Master account that would enable its holder monitoring, making corrections,editing code..., and all the maintenance work.