Creating Countdown in Excel

[psyclone]

Greetings Excel Sages

Please Help... I'm trying to create a counter in excel with a fixed value of 15 minutes and every time that it reaches zero, it will put an incremental integer on a specified cell then it should loop back to start the counter. i i also wanted to put a start button and end button.

i got this from another thread but this one displays a message instead

Sub Countup()
Dim CountDown As Date
CountDown = Now + TimeValue("00:00:01")
Application.OnTime CountDown, "Realcount"
End Sub

Sub Realcount()
Dim count As Range
Set count = [a1]
count.Value = count.Value - TimeSerial(0, 0, 1)
If count <= 0 Then
MsgBox "Countdown complete."
Exit Sub
End If
Call Countup
End Sub

 

[ttdk1]

Sub Countup()  Dim CountDown As Date
  CountDown = Now + TimeValue("00:00:01")
  Application.OnTime CountDown, "Realcount"
End Sub


Sub Realcount()
  Dim count As Range


  Set count = [A1]
  Sheets("Sheet1").Range("A1").NumberFormat = "mm:ss"
  count.Value = count.Value - TimeSerial(0, 0, 1)
  If count <= 0 Then
    Sheets("Sheet1").Range("B1").Value = Sheets("Sheet1").Range("B1").Value + 1
    count.Value = TimeSerial(0, 15, 0)
  End If
  Call Countup
End Sub

 

[psyclone]

Sub Countup()  Dim CountDown As Date
  CountDown = Now + TimeValue("00:00:01")
  Application.OnTime CountDown, "Realcount"
End Sub


Sub Realcount()
  Dim count As Range


  Set count = [A1]
  Sheets("Sheet1").Range("A1").NumberFormat = "mm:ss"
  count.Value = count.Value - TimeSerial(0, 0, 1)
  If count <= 0 Then
    Sheets("Sheet1").Range("B1").Value = Sheets("Sheet1").Range("B1").Value + 1
    count.Value = TimeSerial(0, 15, 0)
  End If
  Call Countup
End Sub

Thank you for the quick reply. However, I encountered an error "Type Mismatch" and this is highlighted > count.Value = count.Value - TimeSerial(0, 0, 1)

 

[ttdk1]

Runs Just fine. I'm using excel 2010

here's a little revision

Sub Countup()  Dim CountDown As Date
  CountDown = Now + TimeValue("00:00:01")
  Application.OnTime CountDown, "Realcount"
End Sub


Sub Realcount()
  With Sheets("Sheet1")
    .Range("A1").NumberFormat = "mm:ss"
    .Range("A1").Value = .Range("A1").Value - TimeSerial(0, 0, 1)
  If .Range("A1").Value <= 0 Then
    .Range("B1").Value = .Range("B1").Value + 1
    .Range("A1").Value = TimeSerial(0, 15, 0)
  End If
  End With
  Call Countup
End Sub

 

[psyclone]

Runs Just fine. I'm using excel 2010

here's a little revision

Sub Countup()  Dim CountDown As Date
  CountDown = Now + TimeValue("00:00:01")
  Application.OnTime CountDown, "Realcount"
End Sub


Sub Realcount()
  With Sheets("Sheet1")
    .Range("A1").NumberFormat = "mm:ss"
    .Range("A1").Value = .Range("A1").Value - TimeSerial(0, 0, 1)
  If .Range("A1").Value <= 0 Then
    .Range("B1").Value = .Range("B1").Value + 1
    .Range("A1").Value = TimeSerial(0, 15, 0)
  End If
  End With
  Call Countup
End Sub

thanks ttdk1. probably the reason why i got the error is when i switch to another excel file while the macro is running since i did not select "this workbook" when i run the macro... silly me, sorry.

now it's working fine using the simple code. thanks