Just found a potential fatal flaw. The cell that displays the count down
stops if I input anything else on the sheet. This won't work for me since my
users will be entering data on multiple worksheets while this is supposed to
be counting down. The timer needs to keep going until a user clicks stop or
it just runs down.
Don't know if anyone can help with this problem, but it kills my goal with
this. Any other solution would be helpful.
"dgold82" wrote:
I found great VBA code online for a countdown timer that fits my need
perfectly. He http://www.vbaexpress.com/kb/getarti...b_id=478#instr
I'm a beginner when it comes to writing code and can use some help tweaking
it for my needs. I would like to do 2 things with the code:
1. Keep the whole thing on one sheet (i.e. start and stop button and inputs
on one sheet).
2. Change the countdown format display in mm:ss format.
Here is the code without going to the website:
Sheet1
________
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Status As Boolean
Private Sub cmdStart_Click()
Status = True
Dim WarningTime As Integer
Dim Period As Double
Dim MyTime As Double
With Sheets("Main")
If (.Cells(5, 1) = "") Then
WarningTime = .Cells(5, 4)
Else
WarningTime = .Cells(5, 1)
End If
If (.Cells(8, 1) = "") Then
Period = .Cells(8, 4)
Else
Period = .Cells(8, 1)
End If
End With
If (Period < 0.01) Then Period = 0.01
With Sheets("Counter").Cells(2, 1)
.FormatConditions.Delete
.FormatConditions.Add xlCellValue, xlLessEqual, WarningTime
With .FormatConditions(1).Font
.Bold = True
.ColorIndex = 3
End With
.NumberFormat = Choose(Log(Period) / Log(10) + 3, "0.00", "0.0", "0")
.Value = Sheets("Main").Cells(2, 1).Value + Period
Sheets("Counter").Activate
While (.Value Period And Status)
.Value = .Value - Period
MyTime = .Value
For i = 1 To 100 * Period
Sleep 10
MyTime = MyTime - 0.01
If (MyTime <= 0) Then Exit For
DoEvents
Next i
Wend
If (.Value <= Period) Then .Value = "Time Up!"
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Row = 2 And Target.Column = 1) Then
Cells(5, 1).Value = Cells(5, 4).Value
End If
End Sub
Sheet 2
_________________
Private Sub cmdStop_Click()
Sheets("Counter").Cells(2, 1).FormatConditions.Delete
Sheets("Main").Status = False
Sheets("Main").Activate
End Sub