PDA

View Full Version : Updating sheet automatically periodically



tneva82
07-06-2011, 01:54 AM
Hello.

I need to get the worksheets updated when day changes. What would be efficient way to do that? I tried having forever loop which checks is date different to previous date and if yes refresh. At the loops end I do "DoEvents".

Problem: Maybe it's just too much load for computer to have macro running there forever but the worksheet acts oddly then. I have parts of OTHER sheet showing up on other sheet until I select area etc.

So since that doesn't seem to work any suggestions? Is there timer event available? Something else?

Kenneth Hobs
07-06-2011, 05:32 AM
I would suggest just checking it when the workbook opens. To do it this way, put this into sheet1's cell A1. =TODAY()

In the VBE, insert a Module and add:
Option Explicit

'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
Public glb_origCalculationMode As Integer

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

In VBE, select the object from View > Project Explorer, add:
Option Explicit

Private Sub Workbook_Open()
If Worksheets("Sheet1").Range("A1").Value <> Worksheets("Sheet1").Range("A2").Value Then
On Error GoTo EndNow
SpeedOn
Worksheets("Sheet1").Range("A2").Value = Date
'*** Your stuff begin

'*** Your stuff end
MsgBox "Refresh Completed!"
End If
EndNow:
SpeedOff
End Sub


You can change the cell and sheet references as needed. Obviously, you would add your refresh code between the comment lines.

tneva82
07-06-2011, 05:59 AM
It specifically needs to refresh automatically. Problem: Sheet open thorough night, worker comes up, sheet does not have new data available. Data is there if he closes excel and restarts it but since that doesn't seem to be acceptable solution I need to figure out something else.

Application.OnTime RunWhen, "msgticker", , True

This seems promising enough. Currently running one to see if it works. Tomorrow morning we'll see.

Kenneth Hobs
07-07-2011, 08:33 AM
You don't have to wait until morning. Change the computer's date and time to test those things.