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

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.

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

On Monday, October 15, 2012 11:56:30 AM UTC-5, Chipperzs wrote:
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





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

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







--

Chipperzs

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