Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Unanswered: Creating a user adjustable countdown timer

    Let me preface that I normally create the tools I use (like this countdown tool) in Access but my employer is scaling back the number of licenses they will maintain for Access and since this tool has been useful for a number of users I'd like to make something in Excel that does what I need it to do. I should also preface, even in Access VBA, I'm pretty much a novice on the edge of intermediate but totally self (and forum--thanks guys) taught.

    OK... here's the setup--I want to create a form driven tool in Excel that auto loads when the macro-enables Excel 2007 spreadsheet file opens. I found some posts (on this board and elsewhere) which describe how to create a simple countdown on a form. Here's where I began:

    in ThisWorkbook I've got the following code:
    Code:
    Sub Workbook_Open()
        Application.Caption = "Timer Application"
        Sheets("Timer").Select
        ActiveWindow.WindowState = xlMinimized
        frmTimer.Show
    End Sub
    This minimizes the worksheet (there's only one in this workbook) and it shows the form "frmTimer" which is where everything will happen. The form itself has only two elements on it: a command button and a label. The command button, "cmdStart" runs the timer and the label, "lblTime" holds the value of the amound of time that remains. the label has no caption to start with but the caption is later filled in using vba.

    The onclick for the cmdStart button is as follows:
    Code:
    Private Sub cmdStart_Click()
        ' when the start button is clicked, hide the start button so it isn't clicked again while the timer is running
        frmTimer.cmdStart.Visible = False
        ' set the caption of lblTime to the amount of time that will be counted down in seconds
        frmTimer.lblTime.Caption = 30
        ' start the countdown
        StartTimer
    End Sub
    I then store the stuff for doing the counting in a module. It looks like this:

    Code:
    Public Sub StartTimer()
        Application.OnTime DateAdd("s", 1, Now), "RunTimer"
    End Sub
    
    Public Sub RunTimer()
        frmTimer.lblTime.Caption = CInt(frmTimer.lblTime.Caption) - 1
        If frmTimer.lblTime.Caption > 0 Then
            StartTimer
        Else
            frmTimer.cmdStart.Visible = True
        End If
    End Sub
    All of this works nicely. Trouble is I want more:

    1) I want the user to be able to enter a number of minutes into a text box on the form and then when the user clicks start I want that number of minutes counted down.

    2) I want the minutes to count down in minutes and seconds rather than converting the minutes to seconds and just countind seconds (5 minutes could be counted down as 300 seconds but at a quick glance it can be difficult for a user to reverse the math and report how much time remains).

    3) I want a + and a - button once the counter is running that allows the user to add or subtract a minute from the remaining time (with the - disappearing when the timer drops below 1 minute).

    4) If the user clicks the + or - button, I'd like the value that appears in the text box to change accordingly and display that new number when the counter stops. (If the user entered 5 initially, and clicked the +, I want it to display 6 in the text box when the counter finishes so if the user wants to time the next event equal to the amount of time given to the first event they can.)

    5) Finally, I want a pause button.


    I'm struggling as to where to even begin with the expansion of this project. I'd be greatful for any advice. Thanks!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I believe that this is possible. I'll look at it more closely when I get in from work.

  3. #3
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question update on my progress

    Thanks for looking in to this for me. I wanted to post an update on my progress. I've accomplished 1, 2, 3, and 4 from my original list but I haven't figured out 5 yet (the pause button). On the form I've got a text box ("txtAmount"), a button to add a minute ("cmdAddMin"), a button to subtract a minute ("cmdSubtractMin") and a label to hold the amount during the countdown ("lblAmount"). The buttons to add and subtract a minute and the label for the amount of time are not visible when the timer is not running and then when the timer is running, the start button is not visible, the text box is not visible, the label for the amount of time is visible and depnding on how much time remains, the add and subtract buttons may or may not be visible. Here's what I've got in terms of code--in this workbook I've got:
    Code:
        Sub Workbook_Open()
            Application.Caption = "Ariel's Timer Application"
            Sheets("Timer").Select
            ActiveWindow.WindowState = xlMinimized
            frmTimer.Show
        End Sub
    then in the code for the buttons in the form I've got:
    Code:
    Private Sub cmdAddMin_Click()
        frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) + 1
        frmTimer.lblAmount.Caption = CInt(frmTimer.lblAmount.Caption) + 1
        If frmTimer.cmdSubtractMin.Visible = False Then
            frmTimer.cmdSubtractMin.Visible = True
        End If
    End Sub
    
    Private Sub cmdStart_Click()
        ' when the start button is clicked, hide the start button
        frmTimer.cmdStart.Visible = False
        ' hide the text box and set the amount label to match
        frmTimer.txtAmount.Visible = False
        frmTimer.lblAmount.Visible = True
        frmTimer.lblAmount.Caption = frmTimer.txtAmount.Value
        If frmTimer.lblTimeMin.Caption = "" Then
            If frmTimer.lblTimeSec.Caption = "" Then
                If frmTimer.txtAmount.Value > 0 Then
                    ' set the caption of lblTimeMin to the amount of time
                    ' specified in txtAmount and set the lblTimeSec to zero
                    frmTimer.lblTimeMin.Caption = frmTimer.txtAmount.Value
                    frmTimer.lblTimeSec.Caption = 0
                Else
                End If
            Else
            End If
        Else
        End If
        ' start the countdown
        StartTimer
    End Sub
    
    Private Sub cmdSubtractMin_Click()
        frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) - 1
        frmTimer.lblAmount.Caption = CInt(frmTimer.lblAmount.Caption) - 1
        If frmTimer.cmdSubtractMin.Visible = True Then
            If frmTimer.lblTimeMin.Caption = 0 Then
                frmTimer.cmdSubtractMin.Visible = False
            End If
        End If
    End Sub
    and finally for the code in my module I've got:
    Code:
    Public Sub StartTimer()
        ' runs the RunTimer 1 second from now
        Application.OnTime DateAdd("s", 1, Now), "RunTimer"
    End Sub
    
    Public Sub RunTimer()
        'check to see if min is greater than zero
        If frmTimer.lblTimeMin.Caption > 0 Then
            frmTimer.cmdAddMin.Visible = True
            'if it is check to see if sec is greater than zero
            If frmTimer.lblTimeSec.Caption > 0 Then
                ' if it is run the countdown on the sec only
                ' set the caption of lblTimeSec to 1 less than it is now
                frmTimer.lblTimeSec.Caption = CInt(frmTimer.lblTimeSec.Caption) - 1
                ' do it again
                StartTimer
            Else
                ' if seconds are 0 but mins are not, decrease min by 1
                frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) - 1
                ' and set the seconds to 59
                frmTimer.lblTimeSec.Caption = 59
                ' do it again
                If frmTimer.lblTimeMin.Caption > 0 Then
                    frmTimer.cmdSubtractMin.Visible = True
                End If
                StartTimer
            End If
        Else
            frmTimer.cmdSubtractMin.Visible = False
            ' if min is equal to zero check secs
            If frmTimer.lblTimeSec.Caption > 0 Then
                frmTimer.lblTimeSec.Caption = CInt(frmTimer.lblTimeSec.Caption) - 1
                ' do it again
                StartTimer
            Else
                frmTimer.cmdAddMin.Visible = False
                ' otherwise, show the start button, reset the labels and turn the
                ' text box back on
                frmTimer.cmdStart.Visible = True
                frmTimer.txtAmount.Visible = True
                frmTimer.lblAmount.Visible = False
                frmTimer.txtAmount.Value = frmTimer.lblAmount.Caption
                frmTimer.lblTimeMin.Caption = ""
                frmTimer.lblTimeSec.Caption = ""
            End If
        End If
    End Sub
    Where I'm still struggling is:

    1) implimentation of a pause button

    2) formatting the seconds ("lblTimeSec") so when there are less than 10 seconds left in the countdown it displays it as a two digit number (09, 08, ... 01, 00, etc.).

    I'd also like to have some visual cue--like having the background change colors and flash or something--at certain intervals (like when there's only 2 minutes remaining or when the time is up or something like that).

    Any help would be greatly apreciated! Thanks!

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Okay, can you post a copy of the file? I can work with it then.

  5. #5
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question The file

    Here's that file. As mentioned earlier, it's a macro enabled Excel 2007 file. If anyone needs me to convert this to a legacy file (97-2003) let me know and I'll make the switch. Thanks!
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Sorry; should have said. I'm on 2000

  7. #7
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Legacy version

    No worries. Here's the file as an older version. Thanks for your help!
    Attached Files Attached Files

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Erk.

    I thought that I posted a comment earlier, but apparently not. In any case it doesn't matter, as I realised that I don't need to see the file after all.

    For your pause function, define a global boolean variable. Amend StartTimer to include a line near the beginning to look at this variable, and only run if it's False. For your pause button, set it to toggle the value of the variable between True and False, and when setting it to False it also invokes StartTimer again.

    That should do the trick!

  9. #9
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question I'm not doing this right...

    Thanks for your help. I think I'm almost there but I'm not using the boolean value properly. I get the concept but I guess I'm just not sure how to execute. I've attached my file.

    What I did is created a pause button which starts out invisible and when the start button is clicked becomes visible and which when clicked becomes invisible, a continue button and a reset button that start out invisible and become visible when the pause button is clicked, and put the boolean varPauseOn a the top of my module. When the start button is clicked, the first thing I have in my code is varPauseOn = False. Then in StartTimer I have If varPauseOn = False run the StartTimer but if not, do nothing. Then I have the pause button set the variable to true, set the pause button, add min and subtract min to invisible and set the continue and reset buttons to visible. The continue button sets the pause button to visible, the continue and reset buttons to invisible and varPauseOn to false and then runs StartTimer. Finally, the reset button sets the lblAmount, the continue and reset buttons all to invisible, sets the vaue of txtAmount, lblTimeMin and lblTimeSec to "" and sets txtAmount to visible and sets the start button to visible. The trouble is the timer never stops and so the pause button isn't doing what it's supposed to do and the reset button causes an error (because RunTimer hits "" as the value of lblTimeMin and chokes in the logic statement if lblTimeMin > 0 then....

    Did I mention that I'm extra green when it comes to coding? *GRIN* The code is as follows--ThisWorkbook:
    Code:
        Sub Workbook_Open()
            Application.Caption = "Ariel's Timer Application"
            Sheets("Timer").Select
            ActiveWindow.WindowState = xlMinimized
            frmTimer.Show
        End Sub
    frmTimer:
    Code:
    Private Sub cmdAddMin_Click()
        frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) + 1
        frmTimer.lblAmount.Caption = CInt(frmTimer.lblAmount.Caption) + 1
        If frmTimer.cmdSubtractMin.Visible = False Then
            frmTimer.cmdSubtractMin.Visible = True
        End If
    End Sub
    
    Private Sub cmdContinue_Click()
        varPauseOn = False
        frmTimer.cmdContinue.Visible = False
        frmTimer.cmdReset.Visible = False
        frmTimer.cmdPause.Visible = True
        StartTimer
    End Sub
    
    Private Sub cmdPause_Click()
        varPauseOn = True
        frmTimer.cmdPause.Visible = False
        frmTimer.cmdContinue.Visible = True
        frmTimer.cmdReset.Visible = True
    End Sub
    
    Private Sub cmdReset_Click()
        frmTimer.cmdReset.Visible = False
        frmTimer.cmdAddMin.Visible = False
        frmTimer.cmdSubtractMin.Visible = False
        frmTimer.cmdContinue.Visible = False
        frmTimer.cmdStart.Visible = True
        frmTimer.lblAmount.Visible = True
        frmTimer.txtAmount.Value = ""
        frmTimer.lblTimeMin.Caption = ""
        frmTimer.lblTimeSec.Caption = ""
    End Sub
    
    Private Sub cmdStart_Click()
        varPauseOn = False
        ' when the start button is clicked, hide the start button
        frmTimer.cmdStart.Visible = False
        ' hide the text box and set the amount label to match
        frmTimer.txtAmount.Visible = False
        frmTimer.lblAmount.Visible = True
        frmTimer.lblAmount.Caption = frmTimer.txtAmount.Value
        frmTimer.cmdPause.Visible = True
        If frmTimer.lblTimeMin.Caption = "" Then
            If frmTimer.lblTimeSec.Caption = "" Then
                If frmTimer.txtAmount.Value > 0 Then
                    ' set the caption of lblTimeMin to the amount of time
                    ' specified in txtAmount and set the lblTimeSec to zero
                    frmTimer.lblTimeMin.Caption = frmTimer.txtAmount.Value
                    frmTimer.lblTimeSec.Caption = 0
                Else
                End If
            Else
            End If
        Else
        End If
        ' start the countdown
        StartTimer
    End Sub
    
    Private Sub cmdSubtractMin_Click()
        frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) - 1
        frmTimer.lblAmount.Caption = CInt(frmTimer.lblAmount.Caption) - 1
        If frmTimer.cmdSubtractMin.Visible = True Then
            If frmTimer.lblTimeMin.Caption = 0 Then
                frmTimer.cmdSubtractMin.Visible = False
            End If
        End If
    End Sub
    modTimer:
    Code:
    Dim varPauseOn As Boolean
    
    
    Public Sub StartTimer()
        If varPauseOn = False Then
            ' runs the RunTimer 1 second from now
            Application.OnTime DateAdd("s", 1, Now), "RunTimer"
        Else
        End If
    End Sub
    
    Public Sub RunTimer()
        'check to see if min is greater than zero
        If frmTimer.lblTimeMin.Caption > 0 Then
            frmTimer.cmdAddMin.Visible = True
            'if it is check to see if sec is greater than zero
            If frmTimer.lblTimeSec.Caption > 0 Then
                ' if it is run the countdown on the sec only
                ' set the caption of lblTimeSec to 1 less than it is now
                frmTimer.lblTimeSec.Caption = CInt(frmTimer.lblTimeSec.Caption) - 1
                ' do it again
                StartTimer
            Else
                ' if seconds are 0 but mins are not, decrease min by 1
                frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) - 1
                ' and set the seconds to 59
                frmTimer.lblTimeSec.Caption = 59
                ' do it again
                If frmTimer.lblTimeMin.Caption > 0 Then
                    frmTimer.cmdSubtractMin.Visible = True
                End If
                StartTimer
            End If
        Else
            frmTimer.cmdSubtractMin.Visible = False
            ' if min is equal to zero check secs
            If frmTimer.lblTimeSec.Caption > 0 Then
                frmTimer.lblTimeSec.Caption = CInt(frmTimer.lblTimeSec.Caption) - 1
                ' do it again
                StartTimer
            Else
                frmTimer.cmdAddMin.Visible = False
                ' otherwise, show the start button, reset the labels and turn the
                ' text box back on
                frmTimer.cmdStart.Visible = True
                frmTimer.txtAmount.Visible = True
                frmTimer.lblAmount.Visible = False
                frmTimer.txtAmount.Value = frmTimer.lblAmount.Caption
                frmTimer.lblTimeMin.Caption = ""
                frmTimer.lblTimeSec.Caption = ""
            End If
        End If
    End Sub
    I've also attached the updated file as it exists right now. Any help is greatly appreciated. Thanks!
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Okay, I've downloaded it

    I'm a tad busy at the moment, but I should have time to look at it a bit later.

  11. #11
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Thanks so much!

    I really appreciate any help you can offer. I look forward to hearing more. Thanks!

  12. #12
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Thumbs up All fixed

    All fixed! I've attached my final product in case anyone wants to play with it (the zip file contains both the legacy 97-2003 and the 2007 version. Thanks for the help!
    Attached Files Attached Files

Posting Permissions

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