Timesheet Console
The main console of Excel Timesheet Tracker records time spent on
tasks and sub-tasks for a particular user. Each employee should keep a copy of
this file of his own, either in his system or a shared location for a manager
to supervise. Then, entries can be exported as needed (that’s something each
company may agree: i.e. weekly, monthly, on project terms, etc), as a separate
spreadsheet that can be combined with those of other employees and stored
separately for management purposes.
User Form Entries
User tasks, sub-tasks, and any other time to be recorded will be
entered through a VBA UserForm in Excel Timesheet Tracker. The three available
fields are populated with data from the TaskList worksheet.
This is done with the UserForm_Initialize subroutine by looping
through the number of rows with data (lr) for each category (Tasks, Sub-tasks,
Other), and adding each item to a ComboBox in UserForm1 (this is the default
name, but it can be changed). See the example below for Tasks, and note the ComboBox
for Tasks is named “Tasks” (same for sub-tasks and other).
Dim item
As String, r As Integer, lr As Integer
Dim
TaskList As Worksheet
Set
TaskList = ThisWorkbook.Sheets(“TaskList”)
‘Tasks
lr =
TaskList.Cells(Rows.Count, “A”).End(xlUp).Row
For r
= 2 To lr
item = TaskList.Cells(r, 1).Value
Tasks.AddItem item
Next r
The ‘Add’ button in the user form will add the selected items to
the main console worksheet via the following VBA code (example for Task):
newTask
= UserForm1.Tasks.Text
lr =
cons.Cells(Rows.Count, “B”).End(xlUp).Row
cons.Range(“B”
& lr + 1 & “:E” & lr + 1).Value = newTask
Show Timesheet Entries
Excel Timesheet Tracker checks if entries were already added for
a given date and pops up a warning if so before saving. The user can click ‘Show’
to check the entries for that given date in the “Entries” worksheet. Entries
appear as “Open” until they are exported, in order to track timesheet
submission to the required recipient. When exported, the status changes to
“Closed”. See below an example of timesheet entries.
In both SaveTS and ShowTS procedures the date is taken from three
separate cells in the console worksheet, added together and formatted into a
date variable (DateTS):
Dim DMYTS
As String
Dim
DateTS As Date
DMYTS =
DayTS & "/" & MonthTS & "/" & YearTS
DateTS =
Format(DMYTS, "dd/mm/yyyy")
There are many different ways to input dates though. New versions
of Excel have a cool calendar functionality to add dates by picking from a
calendar.
Export Timesheet Entries
All open timesheet entries in Excel Timesheet Tracker can be
exported to a separate (macro-free) workbook. Those exports could later easily
be put together for a whole team or company to track timesheets.
The macro is set to add and save a new workbook in the same drive
location where the main Excel Timesheet Tracker is stored using the property
“Path”. It names the timesheet exported file after user’s name and surname, and
current date, and if the file does not exists will add the new workbook.
Dim wb1
As Workbook, wb2 As Workbook, TS As Worksheet
Dim
uName As String, uDate as Variant
Dim
drivePath As String, TimesheetWB As String
Dim
OverWriteFile As VbMsgBoxResult
Set wb1
= ThisWorkbook
uName =
cons.Range("C4").Value
uDate =
Format(Date, "ddmmyyyy")
drivePath
= wb1.Path
TimesheetWB
= drivePath & "\Timesheet_" & uName & "_" &
uDate & ".xlsx"
If
Dir(TimesheetWB) <> "" Then
OverWriteFile
= MsgBox("File already exists. Do you want to overwrite?" _
, vbYesNo)
If OverWriteFile = vbYes Then
Workbooks.Open (TimesheetWB)
Set
wb2 = ActiveWorkbook
Set
TS = Sheets("Timesheet")
TS.Cells.Clear
Else
Exit
Sub
End If
Else
Set
wb2 = Workbooks.Add
ActiveSheet.Name = "Timesheet"
wb2.SaveAs Filename:=TimesheetWB
End If
Click Here to download
the Excel Timesheet Tracker.
No comments:
Post a Comment