I would like to create a timer in excel so I can do time and motion studies
quick and easy. It would need a start stop button and each time you stopped
it would start a new time line to continue the process

-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com

Here we go..

Try the below in a new workbook.

1. From menu View>ToolBars>Select Control Toolbox
2. From the control toolbar click 'Command Button'
3. Point to a cell in worksheet and draw the button.
4. Right click the button>Properties and in Properties window enter Caption
as 'Start'
5. Double click the command button and paste the below code within the click
event.

Private Sub CommandButton1_Click()
lngCol = IIf(CommandButton1.Caption = "Stop", 2, 1)
lngRow = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row + 1
Cells(lngRow, lngCol) = Now()
CommandButton1.Caption = IIf(CommandButton1.Caption = "Stop", "Start", "Stop")
End Sub

6. From workbook toolbar 'control toolbox' click on 'Exit Design mode'
8. Button click and try. This should record the start time and end time in
Col A
and B respectively

PS: If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security).

If this post helps click Yes
---------------
Jacob Skaria


"Haych" wrote:

> I would like to create a timer in excel so I can do time and motion studies
> quick and easy. It would need a start stop button and each time you stopped
> it would start a new time line to continue the process

Forgot to mention to format ColA and ColB to display time in date+time in
hh:mm:ss or otherwise the default one would be hh:mm.

I have modified the code as below to reflect the number of seconds between
start and stop in Col C..Test and feedback

Private Sub CommandButton1_Click()
lngCol = IIf(CommandButton1.Caption = "Stop", 2, 1)
lngRow = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row + 1
Cells(lngRow, lngCol) = Now()
If lngCol = 2 Then Cells(lngRow, 3) = _
DateDiff("s", CDate(Cells(lngRow, 1)), Cells(lngRow, 2))
CommandButton1.Caption = IIf(CommandButton1.Caption = "Stop", "Start", "Stop")
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

> Here we go..
>
> Try the below in a new workbook.
>
> 1. From menu View>ToolBars>Select Control Toolbox
> 2. From the control toolbar click 'Command Button'
> 3. Point to a cell in worksheet and draw the button.
> 4. Right click the button>Properties and in Properties window enter Caption
> as 'Start'
> 5. Double click the command button and paste the below code within the click
> event.
>
> Private Sub CommandButton1_Click()
> lngCol = IIf(CommandButton1.Caption = "Stop", 2, 1)
> lngRow = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row + 1
> Cells(lngRow, lngCol) = Now()
> CommandButton1.Caption = IIf(CommandButton1.Caption = "Stop", "Start", "Stop")
> End Sub
>
> 6. From workbook toolbar 'control toolbox' click on 'Exit Design mode'
> 8. Button click and try. This should record the start time and end time in
> Col A
> and B respectively
>
> PS: If you are new to macros set the Security level to low/medium in
> (Tools|Macro|Security).
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Haych" wrote:
>
> > I would like to create a timer in excel so I can do time and motion studies
> > quick and easy. It would need a start stop button and each time you stopped
> > it would start a new time line to continue the process

[misfit1974]

Hey Jacob

This is great, just what I'm looking for to measure work time for various roles in my org. Is there any way to add colour to the Button eg. When you the button is on "Start" it is displayed as Green and when on "stop" it is red????

Originally Posted by Jacob Skaria

Forgot to mention to format ColA and ColB to display time in date+time in
hh:mm:ss or otherwise the default one would be hh:mm.

I have modified the code as below to reflect the number of seconds between
start and stop in Col C..Test and feedback

Private Sub CommandButton1_Click()
lngCol = IIf(CommandButton1.Caption = "Stop", 2, 1)
lngRow = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row + 1
Cells(lngRow, lngCol) = Now()
If lngCol = 2 Then Cells(lngRow, 3) = _
DateDiff("s", CDate(Cells(lngRow, 1)), Cells(lngRow, 2))
CommandButton1.Caption = IIf(CommandButton1.Caption = "Stop", "Start", "Stop")
End Sub



If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

> Here we go..
>
> Try the below in a new workbook.
>
> 1. From menu View>ToolBars>Select Control Toolbox
> 2. From the control toolbar click 'Command Button'
> 3. Point to a cell in worksheet and draw the button.
> 4. Right click the button>Properties and in Properties window enter Caption
> as 'Start'
> 5. Double click the command button and paste the below code within the click
> event.
>
> Private Sub CommandButton1_Click()
> lngCol = IIf(CommandButton1.Caption = "Stop", 2, 1)
> lngRow = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row + 1
> Cells(lngRow, lngCol) = Now()
> CommandButton1.Caption = IIf(CommandButton1.Caption = "Stop", "Start", "Stop")
> End Sub
>
> 6. From workbook toolbar 'control toolbox' click on 'Exit Design mode'
> 8. Button click and try. This should record the start time and end time in
> Col A
> and B respectively
>
> PS: If you are new to macros set the Security level to low/medium in
> (Tools|Macro|Security).
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Haych" wrote:
>
> > I would like to create a timer in excel so I can do time and motion studies
> > quick and easy. It would need a start stop button and each time you stopped
> > it would start a new time line to continue the process