+ Reply to Thread
Results 1 to 6 of 6

timer and countdown

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2021
    Posts
    979

    timer and countdown

    hi,

    how do you create a timer
    in sheet1 there is a start and stop button
    when you click the start button timer starts counting visually
    with a format of Xh Xm Xs in A1
    when you click the stop button the value of Xh Xm Xs gets saved in A2

    when you click the start button again it starts again at 1s
    then when you click the stop button the value of Xh Xm Xs gets saved in A3

    and so on


    in sheet2 input a hour-min-sec value in A1
    or (hour in A1, min in B1, sec in C1)
    then clicking a start button
    the countdown will start
    then display a msgbox after the countdown is over
    for example: when you type 5sec
    after clicking the start button
    it will countdown visually from 5 to 1 sec

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: timer and countdown

    Code on Sheet1 :
    Private Sub CommandButton1_Click()
      Timer_Start
    End Sub
    Private Sub CommandButton2_Click()
      Timer_Stop
    End Sub
    Code on Sheet2 :
    Private Sub CommandButton1_Click()
      Countdown_Start
    End Sub
    Code on Module1 :
    Option Private Module
    Private Timer_isContinue As Boolean, Timer_StartTime As Date, Countdown_Continue As Boolean, Countdown_TargetTime As Date
    Sub Timer_Start()
      Sheet1.CommandButton1.Enabled = False
      Sheet1.CommandButton2.Enabled = True
      Timer_isContinue = True
      Timer_StartTime = Now
      Timer_Update
    End Sub
    Sub Timer_Stop()
      Timer_isContinue = False
      Sheet1.Range("IV1").End(xlToLeft).Offset(, 1).Value = Sheet1.Range("A1").Value
      Sheet1.CommandButton1.Enabled = True
      Sheet1.CommandButton2.Enabled = False
    End Sub
    Sub Timer_Update()
      If Timer_isContinue Then
         Sheet1.Range("A1").Value = Format$(Hour(Now - Timer_StartTime), "00") & " h " & Format$(Minute(Now - Timer_StartTime), "00") & " m " & Format$(Second(Now - Timer_StartTime), "00") & " s"
         Application.OnTime Now + TimeValue("00:00:01"), "Timer_Update"
      End If
    End Sub
    Sub Countdown_Start()
      Sheet2.CommandButton1.Enabled = False
      Countdown_TargetTime = Now + Sheet2.Range("A1").Value
      Countdown_Update
    End Sub
    Sub Countdown_Update()
      Sheet2.Range("A1").Value = Application.Max(Countdown_TargetTime - Now, 0)
      If Countdown_TargetTime - Now <= 0 Then
         Countdown_Continue = False
         Sheet2.CommandButton1.Enabled = True
         MsgBox "Countdown time has been reached"
      Else
         Application.OnTime Now + TimeValue("00:00:01"), "Countdown_Update"
      End If
    End Sub
    Attached Files Attached Files
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2021
    Posts
    979

    Re: timer and countdown

    hi,

    thanks, works great!
    in sheet1 after clicking stop button how do i make it save the time starting at A3, A4, A5 and so on instead
    and not on B1, C1, D1 and so on

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: timer and countdown

    You are welcome, thanks for rep.points.

    Please change the code of this sub :
    Sub Timer_Stop()
      Dim rng As Range
      Timer_isContinue = False
      Set rng = Sheet1.Range("A65000").End(xlUp).Offset(1)
      If rng.Row < 3 Then Set rng = Sheet1.Range("A3")
      rng.Value = Sheet1.Range("A1").Value
      Sheet1.CommandButton1.Enabled = True
      Sheet1.CommandButton2.Enabled = False
    End Sub

  5. #5
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2021
    Posts
    979

    Re: timer and countdown

    thanks again!

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: timer and countdown

    You are welcome, glad to help.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Countdown timer
    By Maxicell in forum Excel General
    Replies: 0
    Last Post: 01-11-2017, 07:18 AM
  2. Countdown timer
    By Trickypottermus in forum Excel General
    Replies: 3
    Last Post: 11-13-2013, 08:09 AM
  3. countdown timer
    By pka in forum Excel General
    Replies: 0
    Last Post: 10-23-2011, 09:18 AM
  4. Countdown Timer
    By colt seavers in forum Excel General
    Replies: 3
    Last Post: 01-12-2010, 12:23 PM
  5. Countdown timer
    By Nemeo in forum Excel General
    Replies: 0
    Last Post: 05-23-2007, 03:12 AM
  6. Countdown Timer
    By Zaphius in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2007, 06:14 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1