Application.OnTime and Sheet as new file

[mysticmario]

Hi I run into this issue with Application on time the code below runs perfectly fine. It selects one of the sheets, copy it to new file, saves it an d closes the file it created:

Private Sub Workbook_Open()

    Application.DisplayAlerts = False
    Sheets("MAIN").Select
    Sheets("Aktualnie zalogowani").Select
    Sheets("Aktualnie zalogowani").Copy
    ChDir "C:\Users\Shelby\Documents\Listy obecności"
    ActiveWorkbook.SaveAs Filename:= _
        ("C:\Users\Shelby\Documents\Listy obecności\Lista obecności" & Format(Now(), "DD-MMM-YYYY") & ".xlsx"), FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close savechanges = False
    Sheets("MAIN").Select
    Application.DisplayAlerts = True
    
            
End Sub

But when I want to add Application.ontime I get a windows error:
that Macro WorkbookSave cannot be run either beacase this sheet doesnt allow macros or all macros are turned off.
Here's the code:

Private Sub Workbook_Open()


    Application.DisplayAlerts = False
    Sheets("MAIN").Select
    Sheets("Aktualnie zalogowani").Select
    Sheets("Aktualnie zalogowani").Copy
    ChDir "C:\Users\Shelby\Documents\Listy obecności"
    ActiveWorkbook.SaveAs Filename:= _
        ("C:\Users\Shelby\Documents\Listy obecności\Lista obecności" & Format(Now(), "DD-MMM-YYYY") & ".xlsx"), FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close savechanges = False
    Sheets("MAIN").Select
    Application.DisplayAlerts = True
Application.OnTime TimeValue("11:05:00"), "WorkbookSave"
    
            
End Sub
Sub WorkbookSave()

    Application.DisplayAlerts = False
    Sheets("MAIN").Select
    Sheets("Aktualnie zalogowani").Select
    Sheets("Aktualnie zalogowani").Copy
    ChDir "C:\Users\Shelby\Documents\Listy obecności"
    ActiveWorkbook.SaveAs Filename:= _
        ("C:\Users\Shelby\Documents\Listy obecności\Lista obecności" & Format(Now(), "DD-MMM-YYYY") & ".xlsx"), FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close savechanges = False
    Sheets("MAIN").Select
    Application.DisplayAlerts = True
    
    
End Sub

Can anyone help with that?

 

[Dan_W]

Hi - judging from the error message you got from Windows, the issue is not with Application.OnTime or with the code at all. The issue relates to your security settings in Excel. It may be the case that the code in one of the workbooks works but not in others because the first workbook is a Trusted Document, whereas the others are not? Or maybe because it's saved a Trusted Location? In any event, you can find out more information about Macros and Security Settings on the MS website - here.

 

[mysticmario]

I allowed all macros globally in excel, added location to trusted and it still deosnt work.
Why is that, that the same code runs fine when i run the macro manually but when i set it as ontime it doesnt work... It does not make sense

 

[Dan_W]

Have you restarted Excel since allowing macros? I can't really take a view as to why it works in situation A versus situation B without understanding more about the workbooks, but the threshold issue for me is needing to resolve the security settings point. I understand that you've allowed macros globally, but there is still some issue with your settings because you would not be getting this error message otherwise. Application.OnTime can be tricky in terms of scheduling it, cancelling it, understanding how it works in terms of whether or not you are attempting to call a subroutine in a private module, for example, but I don't see that Application.OnTime would be causing this particular problem.

 

[mysticmario]

I fixed it.
I had the take the second code from This_Workbook into seperate module and it works like a charm.
Thanks for your time

 

[Dan_W]

understanding how it works in terms of whether or not you are attempting to call a subroutine in a private module

Ahh, so this was the issue then? In the same vein, you can't call a private sub in a userform with Application.Ontime for this reason.