Application.Ontime switching workbooks

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Try this
VBA Code:
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

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
Unfortunately no change.

Try this
VBA Code:
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

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,354
Messages
5,624,195
Members
416,017
Latest member
moritz210

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