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]

how would you refresh?

close and then reopen

 

[Dave E]

Not too sure what you mean. At the moment, I am aware of it, so try and remember on a daily basis to just select another sheet, then go back to the original sheet, and that does the trick. When I am not there however, it is feasible that it will go long periods of time without anyone doing this.

I was kind of hoping there would something I could schedule it to do, similar to hitting F5 in a Internet Explorer or something, every hour, or day...

 

[QuietRiot]

well..

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

Private Sub Workbook_Open()  
Application.OnTime Now + TimeValue("01:00:00"), "MyMacro"
End Sub


Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("01:00:00")
Application.OnTime dTime, "MyMacro"
'the code that does the refreshing.. here
End Sub

just need to figure out a way to "refresh"

 

[VoG]

just need to figure out a way to "refresh"

Would

Calculate

work?

 

[QuietRiot]

just need to figure out a way to "refresh"

Would

Calculate

work?

I bet it would

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

Private Sub Workbook_Open()  
Application.OnTime Now + TimeValue("01:00:00"), "MyMacro" 
End Sub 


Public dTime As Date 
Sub MyMacro() 
dTime = Now + TimeValue("01:00:00") 
Application.OnTime dTime, "MyMacro" 
Calculate
End Sub

not sure how familiar you are with macros..

hit alt - F11 and on the left double click "thisworkbook" and paste these two

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

Private Sub Workbook_Open()  
Application.OnTime Now + TimeValue("01:00:00"), "MyMacro" 
End Sub

then go Insert then Module and when the blank screen comes up paste this

Public dTime As Date 
Sub MyMacro() 
dTime = Now + TimeValue("01:00:00") 
Application.OnTime dTime, "MyMacro" 
Calculate
End Sub

save and close the editor then close your workbook and then reopen it.

***this is not tested.. so maybe change the time to 00:05:00 and check to see if it works in 5 minutes.

 

[Dave E]

Hi. That would appear to be working a treat... still testing, but it looks like its sorted !

For my own interest, any chance you can briefly explain what its doing (I'm just starting out with VBA)... in particular the bit on the workbook _beforeClose confuses me...

Cheers MUCHLY

Dave

 

[VoG]

Hi. That would appear to be working a treat... still testing, but it looks like its sorted !

For my own interest, any chance you can briefly explain what its doing (I'm just starting out with VBA)... in particular the bit on the workbook _beforeClose confuses me...

Cheers MUCHLY

Dave

Basically the WorkBook_BeforeClose() routine stops the timer when you close the workbook. If it didn't your workbook might 'magically' re-open the next time the timer activates.

 

[QuietRiot]

the before close part is cancelling a previously scheduled procedure.. just error checking i would say. if you didnt have this then the Workbook would automatically open every hour after closing it and still run the macro.

here's the link where I got the code..

http://www.ozgrid.com/Excel/run-macro-on-time.htm

you can also search the ontime method which is the driving force of this

 

[Dave E]

Hi Chaps... This is working a treat now. BUT, just one last thing... if you close the spreadsheet before whatever time the timer is set to (1hr in the above example), it throws an error message:

"Run time error 1004"
Method "ontime" of object '_application' failed.

If the spreadsheet is open for longer than an hour, it ds not throw the error.

Cheers again

DAve