Interrupt work (in any active files) to run macro every x minutes - Excel VBA

Question

I have a macro that is supposed to run every x minutes to save the current file to a specified location. I used this link to help write the code -> VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds

However, the macro gets tripped up when another excel file is open & someone is working on it.

Is there a way to interrupt the current operation by the user & inform him using a message box or something so that the macro can then proceed?

Dim TimeToRun, k, RDNum, DestinationFolderName, MasterFile, TestCellNum

Sub ScheduleIt()
    TimeToRun = Now + TimeValue("00:00:10")
    Application.OnTime TimeToRun, "CopyPriceOver"
End Sub

Sub CopyPriceOver()
    Windows(MasterFile).Activate
    Worksheets("Save Log").Activate

    dt = Format(CStr(Now), "mm-dd-yyyy hh-mm-ss")
    MasterFileName = DestinationFolderName & "RD" & RDNum & " " & TestCellNum & " " & dt & ".xlsb"
    ActiveWorkbook.SaveAs Filename:=MasterFileName, FileFormat:=xlExcel12, CreateBackup:=False

    Call ScheduleIt
End Sub
Sub AutoOpen()
    k = 1
    Worksheets("Test Header").Activate
    RDNum = Range("C4").Value
    LR = Cells(Rows.Count, "I").End(xlUp).Row 'Sets the last row
    TestCellNum = Cells(LR, "I").Value
    DestinationFolderName = "\\C:\Directory\"
    MasterFile = ThisWorkbook.Name

    Call ScheduleIt
End Sub

Sub AutoClose()
    Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub

I don't see any code in this post. What have you tried that isn't working so far? — Parrish Husband, Nov 12 '13 at 20:24

score 0 · Accepted Answer · answered Nov 12 '13 at 21:04

Check that the intended workbook is activated, so your save action is only performed on that workbook:

Public Sub OnTimeCallback()
    If ActiveWorkbook.Name = "MyWorkbook.xlsm" Then
        Debug.Print "Save some stuff"
    End If
    alertTime = Now + TimeValue("00:0:02")
    Application.OnTime alertTime, "OnTimeCallback"
End Sub

A better solution would be to deactivate the timer as long as another workbook is active, using the Workbook_Activate/Workbook_Deactivate events.

Interrupt work (in any active files) to run macro every x minutes - Excel VBA

1 Answers1