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

Countdown / Automatic Recalc ?
afg
I've only been programming for about two months now!
can you explain how the above works
Duncan Woods
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
zort15
The help on the OnTime method explains it.