Auto Calculate is on, but I still need to press F9

[Kourada]

I've got a fairly large spreadsheet, with many formulas. Although the AutoCalc is set, the sheet won't Calculate unless I press the F9 key. Many of the formulas in the worksheet are dependant on the =Now() formula auto-updating when the time changes.

I need the cells to update when the time changes (i.e. when the "=NOW()" changes), but the time ("=NOW()") won't change unless I press F9.

How can I make the sheet automaticly press F9 at certain time intervals, when the time intervals on the sheet won't change?

Thanks.....

 

[Barrie Davidson]

Have a look at

http://support.microsoft.com/default.aspx?scid=kb;en-us;154134&Product=xlw

Regards,

 

[Kourada]

Thanks for the quick reply.

I've already installed the Service Releases, but that didn't seem to help. I've replaced the "=" signs in the workbook with new "=" signs; that did seem solve the prolem. I guess I'll simply have to do that from time to time.

 

[searlecourt]

What exactly did you do to get =NOW() function to update without pressing F9? The only idea I had was using OPC to display the system time and date.

 

[blackdiamonds]

Hey there,

Drop this into a new module:

Option Explicit
Dim NextTick
Sub SimulateHittingF9()
Range("A1") = Now
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "SimulateHittingF9"
End Sub

This'll put the current datetime into A1 and update it every second using the OnTime method.

Hope that helps,


-B

 

[blackdiamonds]

actually, that'll cause some problems. check this artile out:
http://www.mrexcel.com/archive/VBA/4217.html