Microsoft Office Forums - View Single Post - Can Excel VBA auto-update a co-shared workbook every ten seconds?

Logit · 10-03-2024, 06:45 PM

The following works here. Paste into a Regular Module. When the workbook opens, the saving macro initiates. When the workbook closes, the macro stops.

Code:

Dim TimeToRun

Sub auto_open()
    Call ScheduleCopyPriceOver
End Sub

Sub ScheduleCopyPriceOver()
On Error Resume Next
    TimeToRun = Now + TimeValue("00:00:05")
    DoEvents
    Application.OnTime TimeToRun, "CopyPriceOver"
End Sub

Sub CopyPriceOver()
On Error Resume Next
    ThisWorkbook.Save
    Call ScheduleCopyPriceOver
End Sub

Sub auto_close()
    On Error Resume Next
    Application.DisplayAlerts = True
    Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub