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!
dBforums
>
Creating a user adjustable countdown timer
04-27-09, 17:26
Creating a user adjustable countdown timer
All fixed
Linear Mode
