how to create a timer in excel

Discussion in 'Microsoft Excel Misc' started by Haych, Jun 30, 2009.

  1. Haych

    Haych Guest

    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
     
    Haych, Jun 30, 2009
    #1
    1. Advertisements

  2. Haych

    Jacob Skaria Guest

    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
     
    Jacob Skaria, Jun 30, 2009
    #2
    1. Advertisements

  3. Haych

    Jacob Skaria Guest

    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
     
    Jacob Skaria, Jun 30, 2009
    #3
  4. Haych

    misfit1974

    Joined:
    Sep 7, 2009
    Messages:
    1
    Likes Received:
    0
    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????

     
    misfit1974, Sep 7, 2009
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Creating a timer or "stop watch" in excel.

    Guest, Jul 16, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    1,980
    Frank Kabel
    Jul 16, 2004
  2. JohnF
    Replies:
    6
    Views:
    220
    Earl Kiosterud
    Sep 23, 2004
  3. JohnF
    Replies:
    1
    Views:
    295
  4. Guest
    Replies:
    1
    Views:
    480
    Tushar Mehta
    Mar 10, 2006
  5. Guest

    How do I insert a timer in an excel worksheet?

    Guest, Jul 3, 2006, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    258
    basstbone
    Mar 6, 2010
Loading...

Share This Page