Timesheet logger

[aateeque]

Hi
I am new to vba. I want to create a timesheet system where on Sheet1 there are two buttons 'Start time' and 'End time'.
Whenever 'Start time' is pressed the current date and time (in separate columns) should be inserted. Likewise for 'End time' button, (i.e. the end time value entered in a separate column). The difference of the two time value entered in a final column 'Total hours worked'.

Can anyone help me with this please? Where do I start?
Thanks

[deyken]

Hi There,

You are in luck! About a month or so ago I was quite bored and at the time experimenting with Excel as a Database. So, I wrote myself a little time & attendance VBA Macro. I attached it hereto for you to enjoy.

If you have any questions and/or suggestions for updates from there on, please let me know. (I'm bored again today ;0) )

PS: This file serves as the "Program Interface" and required a separate Excel Worksheet that contains the actual DB "Data" - Since you can only upload 1 file at a time, please check my subsequent post, as I have attached this Database file to that one.

Installation:


Please ensure that both the Attendance_Interface.xls and Database.xls files reside in the same folder root. Where this folder is does not really matter.
Please ensure that your machine has Microsoft's JET Version 4.0 Engine installed. If not, simply download and instaal a copy from www.microsoft.com (http://www.microsoft.com)
Let me know what you think!

Cheers
Deyken

[deyken]

Hi There,

Herewith the Database file as promised...

Let me know if you have questions and suggestions...

Here is the Main Clock IN/OUT Button's VBA Code to give some inside info on how I programmed it:

Private Sub ClockEmployee_Click()
' Running Attendance Clocking Algorithm
Dim IDNo As String
Dim cn As New ADODB.Connection
Dim EMP As New ADODB.Recordset ' Employees Table
Dim Dept As New ADODB.Recordset
Dim i, SrchCount As Integer
Dim WasFound As Boolean
Dim RowStart, Row As Integer

WasFound = False
SrchCount = 0
IDNo = TextBox1.Text
RowStart = 4 ' Default starting row for now
' OPEN DATABASE CONNECTIONS
With cn
.Provider = "MSDASQL"
.ConnectionString = "DSN=xlAttendance;"
.Open
End With


If (OptionButton1.Value = False) And (OptionButton2.Value = False) Then
' No Option were selected - Prompt User
MsgBox "You must select the Clocking Function to Apply!", vbCritical
Else ' An OptionButton (RadioButton) Was selected - Now Start Main Algorithm

' *************************************************************************** ******
' ******************* CLOCK IN FUNCTION *******************************************
If OptionButton1.Value = True Then
' Now check that User entered a searchable ID Number
If IDNo = "" Then
MsgBox "You must enter an ID Number to search for!", vbCritical
Else
' Search for Employee by his/her ID Number
EMP.Open "SELECT * FROM [tblEMPLOYEE$]", cn, adOpenDynamic, adLockOptimistic
' No manually go look for an ID Number that matched
i = EMP.RecordCount
Label14.Caption = "Searching " & i & " Employee Records"
EMP.MoveFirst
Row = NextRow(4)

While Not EMP.EOF
If EMP.Fields("EMP_ID").Value = IDNo Then
' Found the record
With Sheet1
.Cells(Row, "A") = EMP.Fields("EMP_ID").Value
.Cells(Row, "B") = EMP.Fields("EMP_CONO").Value
.Cells(Row, "C") = EMP.Fields("EMP_NAME").Value & " " & EMP.Fields("EMP_LNAME").Value
.Cells(Row, "D") = EMP.Fields("EMP_PHONE").Value
.Cells(Row, "E") = EMP.Fields("EMP_DEPT").Value
.Cells(Row, "F") = EMP.Fields("EMP_POSITION").Value
.Cells(Row, "G") = DateValue(Now)
.Cells(Row, "H") = Time() ' Time clocked in
End With
WasFound = True
EMP.MoveNext
SrchCount = SrchCount + 1
Label14.Caption = "Found after " & SrchCount & " tries"
Else
WasFound = False
EMP.MoveNext
SrchCount = SrchCount + 1
Label14.Caption = "Not found after " & SrchCount & " tries"
End If
Wend
End If
End If ' End of IF TEXT BOX IS EMPTY OR NO
End If ' END OF CLOCK IN FUNCTION
'************************************************************************** **************************
'********************** END OF CLOCK IN FUNCTION ****************************************************



'************************************************************************** **************************
'********************** START OF CLOCK OUT FUNCTION *************************************************
Dim j As Integer
Dim Wage As Double
Dim TotalTime As Single
Dim ClockInTime As Single

If OptionButton2.Value = True Then
' Main Clock Out Algorithm goes here...
IDNo = TextBox1.Text

Label14.Caption = "Clocking Out " & TextBox1.Text
EMP.Open "SELECT * FROM [tblEMPLOYEE$]", cn, adOpenDynamic, adLockOptimistic
If Not IsNull(EMP.Fields("EMP_WAGE").Value) Then
Wage = EMP.Fields("EMP_WAGE").Value
Else
Wage = 0#
End If

With Sheet1
' Now Locate ID Number with the Attendance Sheet
For j = 4 To 100
If (.Cells(j, "A") = IDNo) Then
' Found the Entry in the Attendance Form
' Now locate the Clock Out Column and complete the rest of the row data
.Cells(j, "I") = Time
.Cells(j, "J") = DateDiff("h", .Cells(j, "H"), .Cells(j, "I"))
'If Weekday(DateValue(.Cells(j, "G"))) = 1 Then
' Wage = Wage * 2
'Else
' Wage = Wage
'End If
.Cells(j, "K") = Wage
.Cells(j, "L") = Wage * (.Cells(j, "G"))
End If
Next j
End With
End If ' END OF CLOCK OUT FUNCTION

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
End ' ALL WORK COMPLETED, CLOSE FUNCTION/SUB & USER FORM

End Sub

[aateeque]

Wow thanks! Thats what I want.

[Bob Phillips]

A much simpler approach.

You have to be in the row before clicking the buttons, but not the actual cells.

[deyken]

Hi There,

Mine does not require you to pick a specific cell or row. It simply seeks the next available empty row programmatically and completes the relevant information, which it selects from a separate Excel file (that acts as the "database").

To fully test this, on the Interface file I have a second sheet that carries some ID Numbers (copied form the database file). Simply Copy/Paste this ID number into the text box on the pop-up form (when you clicked either Clock In or Out buttons), paste the ID number and it completes the record for you. I was going to put in a fail-safe evaluation function to prohibit duplicate entries for the same date for both the CLOCK IN and the CLOCK OUT functions.

Either way, if you put a wage into the relevant "database" sheet/table for each "employee" then it would also automatically calculate your employee's wages for that day.

Please use it and let me know what you think.

Cheers
Deyken

[roxygumabao]

Hi! I'm also looking for a timesheet logger. Can I have a copy of what you created? I keep on getting debug errors T_T

[SamT]

This thread is 7yo, and the Forum Database does not keep uploaded files that long.

Please start a new thread. You can refer to this thread with ".vbaexpress.com/forum/showthread.php?34314-Timesheet-logger#post369288"

I am sorry.