PDA

View Full Version : I'm trying to create a time sheet with stopwatch



dilipramadas
02-09-2010, 09:19 AM
Hi,
I've very limited knowledge of excel and no knowledge of VBA. I'm creating a file which looks something like the below:
Tasks Worker # 1 Worker # 2 Worker # 3
Task 1
Task 2
Task 3
Task 4
Task 5
When Worker # 1 Clicks on Task 1, it should start a timer. And when he clicks on the Task # 2, he should get another timer in the Worker # 1 column. I was able to do this.
But I couldn't do it for the Worker # 2 Column.
Please help me with this regard.

lucas
02-09-2010, 09:52 AM
clicks on task 1, meaning a cell, a picture, a button?

What code did you use so far?

when posting code, select it and hit the vba button to format it for the forum.

dilipramadas
02-09-2010, 10:34 AM
Hi Lucas,
I have no idea of VBA. I was trying to create a simple application based on the script found on VBAexpress " create a stopwatch in excel". Its not image. Task is the data in the cell, which when double clicked runs the stop watch in the next cell. Is there a PM facility in here?

lucas
02-09-2010, 10:39 AM
There is a PM facility here but we would rather keep all discussions public and in the forum for all to learn from.

If you have a file you wish to attach to a post, hit go advanced and scroll down and look for a button that says "manage attachments"

You said you had it partially working so I was just wanting to see what you had to work from if possible.

lucas
02-09-2010, 10:40 AM
Is this the kb entry you were looking at?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=242

dilipramadas
02-09-2010, 10:44 AM
This is the file I have been working on. The stopwatch script works really well, I tried to tweak it but without any success.

lucas
02-09-2010, 11:33 AM
How do you want to trigger the other workers. You are already clicking on task 1 to run it for worker one.

seems it would be better if you clicked on worker one cell to make it work for worker one.

dilipramadas
02-09-2010, 11:50 AM
That is where i'm lost! :)

Public stopMe As Boolean
Public resetMe As Boolean
Public myVal As Variant
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 Then
If Target.Value = myVal And Target.Value <> "" Then
'Changed
Dim startTime, finishTime, totalTime, timeRow
startTime = Timer
stopMe = False
resetMe = False
myTime = Target.Offset(, 2).Value
Target.Offset(, 1).Select
startMe:
DoEvents
timeRow = Target.Row
finishTime = Timer
totalTime = finishTime - startTime
Target.Offset(, 1).Value = Format(myTime + totalTime, "0.0000") & " Seconds"
If resetMe = True Then
Target.Offset(, 1).Value = 0
Target.Offset(, 2).Value = 1
stopMe = True
End If
If Not stopMe = True Then
Target.Offset(, 2).Value = totalTime
GoTo startMe
End If
Cancel = True
End
Else
'Not Changed
stopMe = True
Cancel = True
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myVal = Target.Value
End Sub



This is the code i'm currently using. You'll notice i've not tweaked it much. I'm attaching another file with changed format, which will probably be my last choice. Is there a way I can change the "Task 1" to a button which activates the timer?

lucas
02-09-2010, 11:58 AM
let's just take column 10 for now. You want to run 4 timers from one button on that row?

lucas
02-09-2010, 11:59 AM
I meant row 10, not column 10. Sorry.

dilipramadas
02-09-2010, 12:09 PM
ok. I'm confused.
But i'm uploading another file with button. Please check that once.

Aussiebear
02-09-2010, 07:41 PM
Why not just double click on each task per worker to start and or stop the timer? I'm assuming that each worker will only be attempting to complete one task at a time.

At the moment you have a button with no code attached to it so it can't actually trigger the event.

dilipramadas
02-09-2010, 10:20 PM
I tried working with the button also. But everytime I clicked on the button it took me to the VBE! can you tell me what am i missing there? I'm trying my best to understand this :D

mdmackillop
02-10-2010, 03:43 PM
You have some working code. What you are missing is the fundemental explanation of what you are trying to achieve. The code is not attached to the button, so at present it serves no purpose; Double Clicking does trigger the code though.
Please take the time to explain in detail, and I'm sure your code can be adapted to suit.

Aussiebear
02-11-2010, 06:05 AM
The original file works when you double click on each individual task. To stop the timer for task 1 you need to double click on the next task which then starts the timer for task 2. Double clicking on the blank cell below Task 15 stops the timer. Is this what you what?

dilipramadas
02-11-2010, 09:53 AM
Yes. The file is working exactly the way you mentioned. I want it to be more like an application or a software, hence the unsuccessful experiment with the button :)

Aussiebear
02-11-2010, 03:17 PM
Okay, can I make another suggestion here? Write down in detail what you think you require. By that I mean you need to include who activates the timer, does the timer data need to be stored anywhere once its calculated, do you really need the precision currently supplied by the timer etc. Then a before and after example within a workbook would also be helpful