Automatically refresh a sheet every hour?

[Dave E]

Hi.

Not sure if this is possible... sure it will be... I have a workseet that is basically a massive list of orders that have been placed. I have some conditional formatting based on cells that use the today() function to work out how long (in days) since the order was placed.

Trouble is, the spreadsheet is always open, and is never closed down... so it does not refresh automatically. So orders that should go red to indicate a week since they've been placed, don't!

I don't want to rely on people refreshing the screen. Ideally, I would like it to refresh once a day at 1 minute past midnight, or even every hour...

???

Cheers for any help...

Dave

 

[QuietRiot]

hmm,


try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", False
End Sub

hopefully that does the trick,

anyone else know if that false will ensure the macro doesn't reopen and calculate?

guess you can always test it let us know

 

[Dave E]

Hi.

This does prevent the error message, but regrettably it does make the sheet keep re-opening on the specified interval (If excel is left open)

Sorry to keep bothering you with this, but its a great help!

Cheers

Dave

 

[QuietRiot]

Application.OnTime EarliestTime:=dTime, Procedure:="MyMacro", Schedule:=False

just tested it.. it should work

if not ill try again

 

[Dave E]

I'm afraid still the same

Just throws the error if you close the sheet within 1 minute (I changed the value for testing purposes).

edit: Oh, and it appears to kieep re-opening aswell with the latest code...

 

[Dave E]

Can send the sheet over if it helps...
daveedwards20@tiscali.co.uk

 

[QuietRiot]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
    Application.OnTime EarliestTime:=dTime, Procedure:="MyMacro", Schedule:=False

End Sub

ok, one last attempt.. try it like that with the on error resume next in there.. that seems to work for me just fine.

PS.. is "works a treat" an english (UK) saying?
its not the first time I've seen it on here from the UK guys

 

[Dave E]

Regrettably, no joy... gets rid of the error message, but keeps reopening the sheet.

Never mind

Haha... Yes "Works a treat" must be a UK phrase... just means that it works well.

Cheers

Dave

 

[QuietRiot]

hmm.
I don't get it
this will bother me all day..

if anyone else has any ideas throw them out here

im at work so i can't give out my email, and IT blocks all the free email accounts like yahoo/hotmail etc.

if you can upload it somewhere I'll take a look.

i used all the code above and set it to 1 minute with a message box. every minute a message box poped up. so then i exited the workbook but left excel open. No error messages and i waited like 10 minutes no pop up box and it didn't open the workbook.

 

[Dave E]

hi. PM'd you the file.

 

[QuietRiot]

I tried so many different things I can't figure it out. It has a mind of its own **** it.

anyone else got any ideas on how to end this ontime object correctly?