Application.Ontime switching workbooks

[Afro_Cookie]

This is a continuation of a post that is now buried. Application.OnTime continuously opening my workbook The Code @AlphaFrog helped with is working perfectly.

Each instance of save, the workbook comes to the foreground and then to the background again after the save is completed. While this is minor it is really inconvenient to see something flashing on your screen every 10 seconds.

Not too sure how to phrase this, but can the save feature be run in the background only so that it won't flash each 10 seconds?

 

[GWteB]

Try this

Sub SaveThis()

    Dim bScrn   As Boolean
    
    With Application
        bScrn = .ScreenUpdating
        .ScreenUpdating = False
        .DisplayAlerts = False
        ThisWorkbook.Save
        .DisplayAlerts = True
    
        NextTime = Now + TimeValue("00:00:10")
        .OnTime NextTime, "SaveThis"
        .ScreenUpdating = bScrn
    End With
End Sub

 

[Afro_Cookie]

Unfortunately no change.

Try this

Sub SaveThis()

    Dim bScrn   As Boolean
   
    With Application
        bScrn = .ScreenUpdating
        .ScreenUpdating = False
        .DisplayAlerts = False
        ThisWorkbook.Save
        .DisplayAlerts = True
   
        NextTime = Now + TimeValue("00:00:10")
        .OnTime NextTime, "SaveThis"
        .ScreenUpdating = bScrn
    End With
End Sub

 

[Afro_Cookie]

Update: Does Excel have to switch to the workbook it's saving in order to do the save? Can it be done in the background, like autosave?

 

[GWteB]

Saving a file by means of a macro can always take place in the background. I am not sure (because I've never used the OnTime method till now ...) but I think this is VBA native behavior when using OnTime. Excel seems to switch to the workbook that contains the OnTime macro. I just did a little test and it turns out that even a closed workbook reopens to run an OnTime macro. Therefore an alternative would be to introduce a homemade AddIn for placing the OnTimeSave macro in there, however some adjustments to your code would be neccesary.