In this article, we have created an automated Employee Timesheet Template in Excel. You can use this template to capture the Employee’s Productive hours. We have used several validations and a few VBA macros to enter the current date and time.
Users can fill time only for today’s date. I will not allow entering the time for past or future dates.
Below are macros that we have used to update the time and clear the sheet.
Option Explicit
Sub Update_Current_Time()
Dim sh As Worksheet
Set sh = ActiveSheet
'''' Validations''''''''''
Dim rng As Range
Set rng = Selection
If Application.WorksheetFunction.CountBlank(rng) <> 1 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If
If rng.Row < 5 Or rng.Row > 35 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If
If rng.Column < 2 Or rng.Column > 7 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If
If sh.Cells(rng.Row, 1).Value <> Int(Now) Then
MsgBox "You can update the time for today only", vbCritical
Exit Sub
End If
'''''''''''''''''''''
sh.Unprotect "1234"
rng.Value = Now
sh.Protect "1234"
End Sub
Sub Clear_sheet()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Unprotect "1234"
sh.Range("B5:G35").ClearContents
sh.Protect "1234"
End Sub
