Employee Attendance Worksheet

[MFogarty]

I am not sure if it is possible in excel but would like to begin planning a project to create a workbook where employees sign in and put there password. Then I would like a button that they can press that will stamp the time and date in another worksheet assuming the log-in information is correct. A time clock really. I was hoping to get some advice on the best way to go about it from the masters before I begin. One issue I am not sure about right now is how to write to a new line each time. The other is how to put the time in without losing the information everytime it calculates like with a Now() statement. Thanks!

 

[Nimrod]

THE WAY IT WORKS:
Employee's name are listed in Column A of the active sheet.
An employee Double Clicks on their name .
They are greated , by name , and asked if they want to Check-in or Check-out.
The employee is then asked to type in their unique Id.

If all information is correct a hidden sheet records :
- empl name
- date
- time
- type of check-in eg (out or in)

(NOTE) : You do not want empl to type in name as they will always be making mistakes ... just have them click their name and then type in passwd / unique ID

THE SET UP

(A) PASSWORD SHEET
- Make a sheet and name it PassWords
- type employee names into column A
- type employee unique ID into column B
- do not protect the sheet
- make the sheet "xlVeryHidden" (optional)

(B) TIME RECORD SHEET
- Make a sheet and name it TimeLog
- do not protect it
- make the sheet "xlVeryHidden" (optional)

(C) EMPLOYEE NAME SHEET
- Does matter what you name this sheet
- Copy and Paste Employee's name from Password Sheet to column A of this sheet.
-These names must be identical,with Passwd sheet , but the order on the either sheet does not matter.

(D) THE CODE
- Macros must be enabled
- ALL the code MUST be copied into the code window of the specific sheet that you expect the employees to double click their name on (eg Employee Name sheet ).
- The following instructions must be followed carefully ...

TO INSTALL IN SHEET CODE WINDOW:
1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

TO RUN ...
- Double Click on cell in column A of "Employee Name Sheet".
- Note: the cell must have an employee name in it


THE CODE ....

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub

If Target.Column = 1 And Len(Target.Value) > 1 Then

'GET EMPLOYEE NAME FROM COLUMN A
EmplName = Target.Value

'FIND OUT IF CHECK-IN OR CHECK-OUT
Ln1 = "IS THIS A CHECK IN ?" & vbCrLf
Ln2 = "YES : CHECK-IN" & vbCrLf
Ln3 = "NO  : CHECK-OUT" & vbCrLf
ln4 = "CANCEL ENTIRE PROCESS " & vbCrLf
msg = Ln1 & Ln2 & Ln3 & ln4
CheckIn = MsgBox(msg, vbQuestion + vbYesNoCancel, "Hello " & UCase(EmplName))

If CheckIn = vbCancel Then GoTo TheEnd

TryAgain:

'GREET AND ASK EMPL FOR ID NUMBER
Ln1 = "Hello " & EmplName & " ......"
Ln2 = "Please Enter Your Unique ID"
msg = Ln1 & vbCrLf & Ln2
UniqueID = Application.InputBox(msg, "Employee ID")

'EXIT IF EMPL CLICKS CANCEL
If UniqueID = False Then GoTo TheEnd

'TRY AGAIN IF INVALID PASSWORD
If Not ValidNumber(EmplName, UniqueID, "PassWords") Then GoTo TryAgain

'ENTER INFO INTO TIME SHEET
With Sheets("TimeLog")
    NextRow = .Cells(65536, 1).End(xlUp).Row + 1
    
    ' STORE EMPL NAME
    .Cells(NextRow, 1).Value = EmplName
    
    ' STORE DATE
    TheDate = Format(Date, "dd-mm-yy")
    .Cells(NextRow, 2).Value = TheDate
    
    ' STORE TIME
    TheTime = Format(Time, "hh:mm ampm")
    .Cells(NextRow, 3).Value = TheTime
    
    ' STORE TYPE OF LOGIN
    If CheckIn = vbYes Then
     .Cells(NextRow, 4).Value = "IN"
    Else
     .Cells(NextRow, 4).Value = "OUT"
    End If
    
    ' CONFIRM LOGIN
    Ln1 = "Successfull Check-in " & vbCrLf
    Ln2 = "Name :" & vbTab & EmplName & vbCrLf
    Ln3 = "Time :" & vbTab & TheTime & vbCrLf
    ln4 = "Date :" & vbTab & TheDate & vbCrLf
    msg = Ln1 & Ln2 & Ln3 & ln4
    pt = MsgBox(msg, vbInformation, "Process Completed")


End With

TheEnd:
Cancel = True
End If

End Sub


Public Function ValidNumber(EmplName, PassWd, PassWdSheet) As Boolean
With Worksheets(PassWdSheet).Range("a1:a1000")
    Set C = .Find(EmplName, LookIn:=xlValues)
    If Not C Is Nothing Then
        If UCase(C.Offset(0, 1).Value) = UCase(PassWd) Then
        ' EMPL NAME AND PASSWD MATCH
            ValidNumber = True
        Else
        msg = "Invalid ID !!!!!!!!."
        pt = MsgBox(msg, vbCritical, "Sign-in Aborted")
        
        End If
              
   Else
   ' EMPL NAME NOT FOUND ON PASSWORD SHEET
   msg = "The Name " & Chr(34) & EmplName & Chr(34) & " could not be found."
   pt = MsgBox(msg, vbCritical, "Sign-Aborted")
   ValidNumber = False
    End If
End With

End Function