Countdown / Automatic Recalc ?

so...i'm writing a program in Excel for my friend who works at a small pool hall. I've created a countdown (initiated when the user clicks a table) and some other cool stuff.
I posted a pic of the excel file here: http://jeremy.haynie.googlepages.com/rackems.jpg

so right now I've got the countdown cell (17 min. 15 secs in the pic) as a normal worksheet cell...now - start time. and i've got a macro running every x seconds which makes the cell look like a countdown. I've got vlookup functions bringing back the dollar amount.

here is the code i'm using to recalculate and the one to close the workbook. Basically, I've got the recalculate stuck in a loop until the user closes the workbook.

Is there a better way to do this Sometimes the workbook will automatically reopen because the recalc macro runs again.

Sub recalc()
If stopp = 2 Then Exit Sub

Dim wksht As Worksheet
For Each wksht In ActiveWorkbook.Worksheets
wksht.Calculate
Next wksht

update = Now() + Sheets("lookups").Range("updateinterval")
Application.OnTime update, "recalc"
End Sub

Sub tclose()
stopp = 2
Application.Wait (Now + TimeValue("00:00:05"))
'allows time for "recalc" to stop
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub




Answer this question

Countdown / Automatic Recalc ?

  • Meso X

    The command simple cancels any previous Ontime code by setting the Schedule argument to false.

    The help on the OnTime method explains it.


  • Abhishek Chadha

    Hi,

    You need to cancel the Ontime request in order to stop any queued requests from firing up the workbook again.

    Private m_datLastRan As Date
    Sub recalc()

    Dim wksht As Worksheet

    For Each wksht In ActiveWorkbook.Worksheets
    wksht.Calculate
    Next wksht

    m_datLastRan = Now() + Sheets("lookups").Range("updateinterval")
    Application.OnTime m_datLastRan, "recalc"

    End Sub

    Sub tclose()

    ' cancels Ontime
    Application.OnTime m_datLastRan, "recalc", , False

    ActiveWorkbook.Save
    ActiveWorkbook.Close

    End Sub



  • Rob1234

    Thanks Andy!

    I've only been programming for about two months now!

    can you explain how the above works


  • Countdown / Automatic Recalc ?