Require a code for time tracker which cannot be edited. [SOLVED]

[vipersdoom]

Hey Guys,


I'm trying to find a macro which will record the time for a task that i do. To be precise we have different teams involved in the same project and we are trying to find a solution to track the time spent by each of the employee in that particular project. Is it possible to create a macro where, when a employee starts working on the project he can just click on the start button and when is done he can click the stop button and the time will get captured automatically. They should not be able to alter the time cells which shows the time spent on the project. I'll give sample layout of the Design of the sheet.


Example :


Cell A2 - User Name

Cell A3 - Task1

Cell A4 - Date as per the user's system date

Cell A5 - Start Button

Cell A6 - Stop Button

Cell A7 - Time taken(hh:mm:ss) (which gets automatically calculated as per the entries

of A5 & A6 and cannot be edited)


It would be really great if you could give the code. Would really help me complete my work faster.. I'm really new in macro and i seriously don't have any idea how to do this. But i heard from a friend that with the help of macro i might be able to get a solution.

 

[jeffreyweir]

vipersdoom - Very simple to implement. But first, a few quick questions:

1. are you worried about users over-reporting time, or underreporting it?

2. do you need them to be able to pause the clock while they take a bathroom break or lunch break?

3. How long does it normally take to complete tasks? Minutes, Hours, Days, or Weeks?

 

[vipersdoom]

Hi Jeffrey,


Thank you for responding. Below are the answers:


1. are you worried about users over-reporting time, or under reporting it?

- Yes, I'm worried about about both under reporting and over reporting. We need to have a solid. We need to have exact time spent on that particular task.


2. do you need them to be able to pause the clock while they take a bathroom break or lunch break?

- Yes, if you can add this that would be great.


3. How long does it normally take to complete tasks? Minutes, Hours, Days, or Weeks?

- Few projects go for few days as well, so you may keep days as the criteria.

 

[jeffreyweir]

Thinking about this some more I foresee quite a few issues. For instance, what happens if someone forgets to push Start or Stop at the right time? Or if they switch to another project for an hour or two, before resuming work on the current one again? Or they accidentally close the workbook that has the timer code in it?


Or what's to stop them under or overreporting time simply by starting or stopping the timer at an incorrect time?


While it's fairly easy to implement some kind of timing function, if any of the above happen, then a timing function is ultimately no better than just getting people to manually put start and end times against a list of projects.


Your thoughts?

 

[vipersdoom]

Hi Jeffrey,


Basically the problem here is, people join a call unnecessarily as say we are on a project call even if they are not required. So we are trying to implement a time tracker where they will have to put on the time before joining the call or working on the project. And if they have done that, they will be answerable to what exactly they did in the project during that time frame. I believe once we implement the time tracker thing there will be less chaos and since they are unserable, they will not join the calls unnecessarily and actually kill their time with an excuse.


For your question:

1. What happens if someone forgets to push Start or Stop at the right time?

- We will implement this as mandatory and as part of their project work, so if they forget it is their loss and they will not get credit for those many hours.


2. Or if they switch to another project for an hour or two, before resuming work on the current one again?

- If they switch on an another project they will have to start the timer again.

(Is it possible to have tracker which will track the number the times a user has srated and stopped and we can get a cumulative time spent in the entire day?)


3. Or they accidentally close the workbook that has the timer code in it?

- Well this is a good question i didn't think about it before. I don't have a answer for this. Might require your suggestions.


We do not want them to manually enter as they might enter a false enty say for a 1 hour project they might enter 2 hours and sit idle for the next 1 hour. We are trying to eliminate the manual intervention.


I have one more suggestion if that can be implemented. Require your suggestions on this as well.

Can we have a sheet which contains the names of all the user who are involved in the project and a start stop button next to each employee?

 

[vipersdoom]

Hi Jeffrey,


Looking forward for your reply.


Thank you!

 

[vipersdoom]

Hi,


Well i did manage to get a script and edit it according to my needs. Here is the script hope it will help.


Private Sub Button1_Click()

Dim mytime, response

mytime = Now


If Intersect(ActiveCell, Range("C:C")) Is Nothing Then

MsgBox "Please click on Start time or End time column"

Else

If ActiveCell = "" Then

ActiveCell.Value = mytime

Else: Exit Sub


End If


End If

End Sub

 

[SirJB7]

Hi, vipersdoom!

Glad you could solve it. Thanks for your feedback and for sharing your solution with the community, so as people who read this would have the issue or question and the solution or answer as well.

Be welcome back whenever needed or wanted.

Regards!

 

[Shenoi]

Hi,

You didn't mention what error you are getting, or where the error occurs ?

But still, try this - If you have an Event macro, when you double-click a cell, this double-click event fires off. Regardless of what cell you double-click, or if the cell is empty, then the macro finishes.

But your double-click action still attempts to edit in the cell. since your macro protects the sheet, you get a protection error when you try to edit in the cell but that is not a major issue.

Other than this macro technique, there are some time tracking applications are also avaiable. Why don't you try out those applications? I would recommend browsing through Google to find the best one. I am sure there you can find more specialized programs to buy. Some of the best applications are hubstaff, Replicon Software ( http://goo.gl/yGF1mm ), Resue time, etc.

Thanks!