Method 'On Time' of object'_Application failed

  • I have this code as part of ThisWorkbook to open a flashscreen when the file is opened.


    [VB]
    Private Sub Workbook_Open()
    UserForm1.Show
    End Sub
    [/VB]


    The UserForm1 has the following code attached to call the "KillTheForm" routine so that the form disappears after 7 seconds:


    [VB]
    Private Sub UserForm_Activate()
    Application.OnTime Now + TimeValue("00:00:07"), "KillTheForm"
    End Sub
    [/VB]


    This is the "KillTheForm" code:


    [VB]
    Private Sub KillTheForm()
    Unload UserForm1
    End Sub
    [/VB]


    The code works properly on my machine but when I emailed the workbook to a friend, he got Error 1004 ... Method 'On Time' of object'_Application failed. When he opened the file directly from the email, the splash screen appeared and then disappeared as it should after 7 seconds. However, when he clicked the ENABLE EDITING button in Excel 2010, he got the error message. After clicking END in the error screen, the macros in the workbook worked as they should and everything worked properly. If he saves the file, the error occurs when the file is opened the first time only. If he then closes it and opens it a second time, the ENABLE EDITING BUTTON DOESN'T APPEAR and neither does the error. Suggestions would be greatly appreciated.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Method 'On Time' of object'_Application failed


    After a little trial and error, I found the cause of the error. It is not enough to 'Enable all Macros' and 'Trust access to VBA project object model' in Excel's Macro Security, you must also disable Protected View. When I did all these things, everything worked perfectly. Just remember to re-set the security settings back to what they were afterwards to maximize protection.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.