+ Reply to Thread
Results 1 to 6 of 6

Application.Ontime running as soon as WB is opened.

  1. #1
    Registered User
    Join Date
    10-18-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    7

    Application.Ontime running as soon as WB is opened.

    I have this code written in the "ThisWorkbook" section of MS Excel Objects in VBA.

    Private Sub Workbook_Open()
    Application.OnTime TimeValue("03:00:00") + TimeValue("23:59:00"), "MyMacro"

    End Sub



    My understanding is that this should run "MyMacro" at 24 hours after 3:00 am. When I open the workbook now (8:59am) it runs MyMacro instantly.

    Can anyone help me out with this?

    I did test run this a couple days ago and it ran fine over night. In the test run I had it run at 5:45 am (actually 2:00 am + 3:45 hours). Durring the test run I didn't have the problem where it would auto run the macro when I opened the WB.

    Thanks in advance,
    Jason

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Application.Ontime running as soon as WB is opened.

    Not sure why there's the TimeValue("03:00:00") + TimeValue("23:59:00") for the time it should be run... If you want it to run at 3 AM, it should just be:


    Application.OnTime TimeValue("03:00:00"), "MyMacro"
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Registered User
    Join Date
    10-18-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Application.Ontime running as soon as WB is opened.

    Miraun,
    I want it to run at 3am 2 days from now. This is at work. I want the application to run at 3am on Sunday (people are occasionally working on Saturday). But I may not be at work on Saturday so my intent is to open the WB on Friday afternoon and have it auto run at 3am on Sunday (not 3am Saturday).

  4. #4
    Forum Contributor
    Join Date
    01-20-2012
    Location
    Amsterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    186

    Re: Application.Ontime running as soon as WB is opened.

    Hi Jason,

    If you open your sheet on friday, this will work:


    Application.OnTime Date + TimeSerial(51, 0, 0), "MyMacro"

    Regards,
    Rick

  5. #5
    Registered User
    Join Date
    10-18-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Application.Ontime running as soon as WB is opened.

    Thanks that appears to work (I wont know until I get to work on Monday). The only question I have is there a way to run this macro at a specific time? The way you have it written I have to open the WB at 8am on Friday for the macro to run at 3am on Sunday. If I open the WB any other time after 8am it will just run 51 hours from that time I believe.

    This will be fine this week due to when the other information is scheduled to be sent in (ie I dont care if the macro runs at 3am or noon on Sunday). But I'd like to find a way to run it at a given time, where it doesn't matter what time I open the WB on Friday.

    I'm not sure if that is possible or not.

    Thank you for your help.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Application.Ontime running as soon as WB is opened.

    that code should run at 3am in 2 days from the date you open the workbook
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1