Automatically refresh a sheet every hour?

Dave E

New Member
Joined
Apr 3, 2006
Messages
40
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Dave E

New Member
Joined
Apr 3, 2006
Messages
40
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-known Member
Joined
May 18, 2007
Messages
1,077
well..

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

just need to figure out a way to "refresh"

Would

Code:
Calculate

work?
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
just need to figure out a way to "refresh"

Would

Code:
Calculate

work?

I bet it would

Code:
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
Code:
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
Code:
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

New Member
Joined
Apr 3, 2006
Messages
40

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Well-known Member
Joined
May 18, 2007
Messages
1,077
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

New Member
Joined
Apr 3, 2006
Messages
40
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,273
Members
414,049
Latest member
MisterExcel26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top