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

Hi,

You may not require event procedure such as worksheet_change for this piece of work.
Rather use ActiveX control (Toggle Button). and put your code in the togglebutton_click event. You can use the togglebutton value property to decide what happens when a toggle button is in the pressed or depressed state.
Togglebutton.value = True means it is in pressed state
Togglebutton.value = False means it is in a depressed state
using this you should be able to customize your code.

I am sure you can work out the calculations on your own but let me know if you are stuck somewhere.

Thank You,
Prashant

jack_n_bub

Please elaberate on how to do this...
I've added a zip file with a copy of what I have created so far if anyboudy wants to look at it and reply with a working version.

Thanks again all,

Hi There,

See Attached. I hope this contains everything you were looking for.

Let me know if this helps you.

Thank You,
Prashant

Prashant your awsome!

The only change I made was an or statmet to the formula in column A so that nothing is calculated if either C3 or E3 are blank:
=IF(OR(C3="",E3=""),"",IFERROR(DATEDIF(C3,E3,"D")& " Days "&ROUND((E3-C3)*24,1)&" Hours "&ROUND((E3-C3)*24*60,1)&" Minutes",""))

It looks like the only thing I was missing was the VBA code linked to the togglebutton action that needed to be placed in the Tab's VBA code.

-------------------------------------------------------
Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
Range(ToggleButton1.LinkedCell).Offset(0, 1) = Now
Else
Range(ToggleButton1.LinkedCell).Offset(0, 1) = ""
End If
End Sub

Private Sub ToggleButton2_Click()
If ToggleButton2.Value = True Then
Range(ToggleButton2.LinkedCell).Offset(0, 1) = Now
Else
Range(ToggleButton2.LinkedCell).Offset(0, 1) = ""
End If
End Sub
-----------------------------------------------------

if that's all that was changed I think I got it!

Thanks again.

I just noticed that the formula in column A is calculating totals:
=IF(OR(C3="",E3=""),"",IFERROR(DATEDIF(C3,E3,"D")& " Days "&ROUND((E3-C3)*24,1)&" Hours "&ROUND((E3-C3)*24*60,1)&" Minutes",""))

if C3= 1pm yesterday and E3 = 3:05pm today it calculates
1 Days 26 Hours 1565 Minutes

it should be calculating:
1 Days 2 Hours 5 Minutes


I think I can fix this but it may take me a little while...
Reply back if you can get this working quickly.

Thanks again.