Previous Topic Next Topic

Need Help Please: Excel Click Timer to time each Work/Task

Solved/Closed
jsioson Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen January 22, 2014 - Sep 3, 2013 at 01:54 AM
jsioson Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen January 22, 2014 - Sep 11, 2013 at 11:47 PM
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 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 3, 2013 at 11:54 AM
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.
0
jsioson Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen January 22, 2014
Sep 4, 2013 at 04:40 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 5, 2013 at 10:22 AM
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
0
jsioson Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen January 22, 2014
Sep 11, 2013 at 11:47 PM
Hi Trowa,

Your a genius thanks a bunch!!
0