Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calulating Elapsed Time

    Can anyone provide me with some code to make a label on a userform display the elapsed time? I would appreciate references to "start" and "stop" buttons.

    The plan is to use the following line to tie it to my worksheet...

    Timebox.Value = Range("a" & i).Value
    'where i is an incremental variable representing row number.

    Thanks Millions!
    Drk
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    You didn't say what version of Excel you have. The following works in Excell 2000:

    <pre>Timebox.Value=Application.WorksheetFunction.T ext(ActiveSheet.Cells(i, 1).Value, "[h]:mm")
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    Unfortunately, it's Excel '97...

    I'm having no trouble displaying the current time, I would like to create a stopwatch on the form, which would send the value to a worksheet. I.E. User clicks on start, time begins to elapse, user clicks on stop, time halts, user clicks on submit, and time is submitted to worksheet. I've already got the submission part down, it's generating the stopwatch i'm having issues with...

    Thanks for the reply Legare!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    Did you try it? It might work in Excel 97, but I can't try it. If it doesn't work, try it without the "Application.Worksheetfunction" and see if that works. If that does not work, tell me what error you get in both cases.
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    It returned a type mismatch error in the context you provided, when removing the Application.Worksheetfunction, VBA goes red.. (Doesn't like the Syntax)

    I can display the time by using the following code:

    timebox.value = time()

    i'm looking for a way to elapse time in an object, starting at 0:00:00 any ideas?

    Thanks again!
    Drk
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    OK, then the following should work:

    <pre>Dim stW As String
    stW = Int(ActiveSheet.Cells(i, 1) * 24) & ":"
    stW = stW & Int((ActiveSheet.Cells(i, 1) * 24 - Int(ActiveSheet.Cells(i, 1) * 24)) * 60) & ":"
    stW = stW & Int((ActiveSheet.Cells(i, 1) * 1440 - Int(ActiveSheet.Cells(i, 1) * 1440)) * 60)
    Timebox.Value = stW
    </pre>

    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    Still getting Type Mismatch errors... Is this code intended for Excel 2000?

    What code would I use to start/define a timer event to capture the running time upon btnstarttime_click?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calulating Elapsed Time

    Hi,
    Sorry to butt in, but I have some code that I think does something similar to what you want. This is based on a userform with a textbox called txtTimeElapsed and one commandbutton called cmdRun:
    Private Sub cmdRun_Click()
    Static blnStarted As Boolean, dteStarted As Date, dteStopped As Date
    If blnStarted = False Then
    dteStarted = Now()
    Me.cmdRun.Caption = "Stop"
    Else
    dteStopped = Now()
    Me.cmdRun.Caption = "Start"
    Me.txtTimeElapsed = Format(dteStopped - dteStarted, "hh:mm:ss")
    End If
    blnStarted = Not blnStarted
    End Sub
    There's only one button because it functions as a start/stop button.
    Is that any use?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    That's it!

    Can you think of any way to show the time as it is elapsing?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calulating Elapsed Time

    Took a bit of thought, but yes! Change the code to:
    Private Sub cmdRun_Click()
    Static blnStarted As Boolean, dteStarted As Date, dteStopped As Date
    If blnStarted = False Then
    dteStarted = Now()
    Me.cmdRun.Caption = "Stop"
    Else
    dteStopped = Now()
    With Me
    .cmdRun.Caption = "Start"
    .txtTimeElapsed = Format(dteStopped - dteStarted, "hh:mm:ss")
    End With
    End If
    blnStarted = Not blnStarted
    Do Until blnStarted = False
    Me.txtTimeElapsed = Format(Now() - dteStarted, "hh:mm:ss")
    DoEvents
    Loop
    End Sub
    and you can watch the seconds tick away....
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    Alas!! that's amazing!

    Almost there... i'm noticing that when I click on the stop button at times, it doesn't respond, is this due to the loop? is there anything I can do to make it more 'responsive'?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    Also... (I'm a picky one today..)

    At times, users will have to add callback time to the equation, I can setup a seperate button/field for callback time and add the values for total time, but in the interests of efficiency, is there any way to call the previous value and resume time from there?

    Also, i'm having problems making my text boxes recognize time format... any ideas? I.E. they're returning the .34351etc value excel uses to determine time.

    THANKS MILLIONS!

    Drk
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    I tested the code in Excel 2000, but there isn't anything in there that should not work in Excel 97. Which line are you getting the error on?
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    Rory: Your method will not work if the elapsed time is more than 24 hours. My first thought was to use Format, but it apparently does not recognize the "[h]:mm:ss" as a valid format.
    Legare Coleman

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulating Elapsed Time

    Given that we're dealing with Call times, rory's method is working like a charm! ) (I feel sorry for the poor sap who's taking calls an excess of 24hrs...)

    As for the error, It was on the first line of the code, VBA didn't like the whole thing one bit..

    Thanks Gentlemen!!

    Drk
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •