Need Help Please: Excel Click Timer to time each Work/Task [Solved/Closed]

Report
Posts
3
Registration date
Tuesday September 3, 2013
Status
Member
Last seen
January 22, 2014
-
Posts
3
Registration date
Tuesday September 3, 2013
Status
Member
Last seen
January 22, 2014
-
Hi Excel Pros,

This is my first time to join the excel forums and have seen how helpful you guys are here.

I need to come up with a visual basic code that would able me to click (single click) a specific cell to populate the current time.

Lets say for column A that's where the tasks would reside and column B,D,F,H would be the start time columns while C,G,E and I would be the "end time" columns. It would look like there would be simultaneous timers for each row. Columns L will sum up the the difference of all start/end time columns to show the total time spent.

Conditions are:

-Time should be hh:mm:ss

-Lets say for row 2, a2 would be the task name, once I click b2 (start time), it will populate the current time. Once the employee is done doing the task he may now click on c2 (end time) to populate the current time to show end time. Or if he wants to continue the task, he may click on d2 to start the timer again and e2 to end it and so on..

-the first end time column would not be populated (if accidentally clicked) if the start time column has no time populated in it.

-the start/end timers will not be populated (if clicked) if the prior set of start/end timers are not populated yet.

-if a timer cell is populated, the employee would not be able to amend that time populated (password protect).

Your help will be very much appreciated

Thanks,
JUS

1 reply

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
Hi Jus,

Single-click is not possible, hopefully you can muster the energie
to click one more time :) .

Here you go:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("B2:G" & Rows.Count)) Is Nothing Then Exit Sub

ActiveSheet.Unprotect Password:="Password"

If Target.Value = vbNullString And Target.Offset(0, -1).Value <> vbNullString Then
Target.Value = Time
End If

ActiveSheet.Protect Password:="Password"

End Sub

Remember to unblock the cells you don't want to protect.
Format cells to display time the way you want.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Posts
3
Registration date
Tuesday September 3, 2013
Status
Member
Last seen
January 22, 2014

Hi trowaD,

Thanks so much! The code works great however, each time i double click the target cell, the protect window keeps on popping up.. Is there a way to avoid this? Also, is there a way for me to just choose certain columns/rows to protect instead of protecting the whole sheet?

Thanks a big bunch!
JUS
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
Hi JUS,

To avoid the protect window to pop up you will need to refer to a cell at the end of the code.
So place "Range("B2").Select" or any other cell reference before the End Sub line.

For the other issue go to cell properties, right most tab called security. Here you will find the option to block/unblock cells.
I usually select all cells (ctrl+a) go to cell properties (ctrl+1), unblock, OK.
Then select all cells I want to block and go to cell properties to do so.

Good luck and let me know if modifications are desired.

Best regards,
Trowa
Posts
3
Registration date
Tuesday September 3, 2013
Status
Member
Last seen
January 22, 2014

Hi Trowa,

Your a genius thanks a bunch!!