Need worksheet to refresh so Now() Function Calculates??

[dhowser]

I have a Worksheet that calculates the time people have been gone by subtracting the time they left from a cell with the Function Now() in it. I need to keep this list current without having to press F9 or entering information. Is there a way in Excel 97 to make this work??

[Tom Schreiner]

Hi
Copy this code in your workbook class module.
Will fire a recalculation every ten seconds.
Edit as necc.
This little snippet of code will Calculate all open workbooks.
To change to a specific sheet or range.
Search VBA help for Calculate.

Have a nice day!
Tom

[Tom Schreiner]

Option Explicit
Dim I_Say_Stop

Private Sub Workbook_BeforeClose(Cancel As Boolean)
I_Say_Stop = True
End Sub

Private Sub Workbook_Open()
Dim Start


Start = Timer + 10
Do Until I_Say_Stop
Start = Timer + 10
Do While Timer < Start And I_Say_Stop = False
If Timer = 0 Then _
Start = Timer + 10
DoEvents
Loop
Calculate
Loop
End Sub


[ This Message was edited by: TsTom on 2002-03-30 07:38 ]

[dhowser]

I inserted the text into the workbook code and it worked to begin with. I entered my info and the sheet re-calculated as expected. When I when to delete the info in a cell, I assume that it caused the timer loop to stop. What can I do to fix it??

[Tom Schreiner]

Hi
Sorry,
I should have tested it.
Are the =Now() formulas all entered in one column?
If so, which?
Is there anything else in that column?
I want to place a pause in your Before_DoubleClick event.
If a cell is double clicked the formula =Now() will be entered, if =now() is already in that cell, then it would be deleted.
Do you understand?
Will need to briefly disable Calculation when editing certain cells.
I tried to trap the error but could not.
Is generating an application defined error.
Not a VBA error?
I Emailed you
Tom