Two Toggle Buttons to calculate Day(s) Hour(s) Minute(s) between entry time

[Chipperzs]

I'm looking for a way to use two toggle buttons to help users calculat
the time it takes them to complete a task using a spreadsheet.

I've created two toggle buttons and placed them over Cells C
(ToggleButton1) and D7 (ToggleButton2)
I've changed the captions on the buttons to ""
I've linked each toggle button to the cell that it's contained in
I've changed the toggle button size so that it fits into a 12x12 size
cell

Column A is the duration
Column B is the first toggle button
Column C is the date the first toggle button was depressed
Column D is the second toggle button
Column F is the date the second toggle button was depressed

Columns C,E are formatted to display the date as "m/d/yyyy h:mm"
I have this formula in Column A [=IF(C9="","",IF(E9="","",E9-C9))]

I understand that you can apply VBA to the worksheet tab so that when
specific column changes the date is entered into the targeted column

I'm using...
Private Sub Worksheet_Change(ByVal Target As Range)
Col = Left(Target.Address, 2)
If Col = "$D" Then Target.Offset(0, 1) = Now
If Col = "$B" Then Target.Offset(0, 1) = Now
End Sub

My problems are
1) Changeing the toggle buttons do not activate a Worksheet Change tha
then puts the current date and time into the desired column/cell
(I don't want the dates to change when I close, reopen, recalculate
etc. I only want the date/time recoreded for when the toggle button
was/is depressed)
2) I don't want to see a date if the toggle button is in the norma
(un-depressed) state.
3) I want to create more toggle buttons in other locations of th
spreadsheet (using the same columns for data entry/calculations)
4) I want the calculated duration (Column A) to show the number o
day(s) Hour(s) Minute(s)

I hope I've been specific enough with this question.

Thanks for your help in advanc

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Don Guillett]

I'm looking for a way to use two toggle buttons to help users calculate

the time it takes them to complete a task using a spreadsheet.



I've created two toggle buttons and placed them over Cells C7

(ToggleButton1) and D7 (ToggleButton2)

I've changed the captions on the buttons to ""

I've linked each toggle button to the cell that it's contained in

I've changed the toggle button size so that it fits into a 12x12 sized

cell



Column A is the duration

Column B is the first toggle button

Column C is the date the first toggle button was depressed

Column D is the second toggle button

Column F is the date the second toggle button was depressed



Columns C,E are formatted to display the date as "m/d/yyyy h:mm"

I have this formula in Column A [=IF(C9="","",IF(E9="","",E9-C9))]



I understand that you can apply VBA to the worksheet tab so that when a

specific column changes the date is entered into the targeted column



I'm using...

Private Sub Worksheet_Change(ByVal Target As Range)

Col = Left(Target.Address, 2)

If Col = "$D" Then Target.Offset(0, 1) = Now

If Col = "$B" Then Target.Offset(0, 1) = Now

End Sub



My problems are

1) Changeing the toggle buttons do not activate a Worksheet Change that

then puts the current date and time into the desired column/cell

(I don't want the dates to change when I close, reopen, recalculate,

etc. I only want the date/time recoreded for when the toggle buttone

was/is depressed)

2) I don't want to see a date if the toggle button is in the normal

(un-depressed) state.

3) I want to create more toggle buttons in other locations of the

spreadsheet (using the same columns for data entry/calculations)

4) I want the calculated duration (Column A) to show the number of

day(s) Hour(s) Minute(s)



I hope I've been specific enough with this question.



Thanks for your help in advance





+-------------------------------------------------------------------+

+-------------------------------------------------------------------+

A worksheet_change event only works when you change a cell. Instead of the macro buttons why not just use a double_click event shen you click on the start column it puts in the time and when you click on the stop button it puts in the time and calculates the difference in the cell to the right....
Or, even use just a selection change event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Or Target.Row < 2 Then Exit Sub
If Target.Column = 4 Then Target = Time
If Target.Column = 5 And Target.Offset(, -1) > 0 Then
Target = Time
Target.Offset(, 1) = Format(Target - Target.Offset(, -1), "[hh]:mm:ss")
End If
End Sub