+ Reply to Thread
Results 1 to 10 of 10

Delay vba from running upon opening file

  1. #1
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Delay vba from running upon opening file

    This is an interesting one, but hopefully an easy fix.

    I have the following code below. It automatically closes a workbook after a set time, and works fine in doing so.

    Problem is, it runs as soon as the workbook is opened... because of this, it seems to run before excel executes any initial warnings or yes/no pop ups. For instance, Security warning: "Do you want to make this file a Trusted Document?" only pops up after I stop the code... I want to delay the code below from running for say 30 seconds so that this warning can be submitted.
    Otherwise, it acts like the warning is running in the background and therefore will not let the user click any of the toolbars/buttons.

    I have tried
    Please Login or Register  to view this content.
    but this did not work and just span the cursor for 30 secs.

    Please amend code below with added delay if possible/or adjust code if it could be written better and current issue could be avoided. Thanks in advance.


    Please Login or Register  to view this content.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Delay vba from running upon opening file

    Hi,

    Why don't you use application.ontime for this?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Delay vba from running upon opening file

    Can you explain what the purpose of the timer is?

    The way I read it,
    Please Login or Register  to view this content.
    will delay opening the spreadsheet by TotalTimeInMinutes, which doesn't make much sense.

  4. #4
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Delay vba from running upon opening file

    Quote Originally Posted by xlnitwit View Post
    Hi,

    Why don't you use application.ontime for this?
    I tried application.ontime initially but I could not get it to work after many attempts so found the code which I am using above. application.ontime may suit better but I didn't have much luck with it.

    If you feel you could adapt what I am after (below) to application.ontime then that would be ideal.

    Quote Originally Posted by mgs73 View Post
    Can you explain what the purpose of the timer is?

    The way I read it,
    Please Login or Register  to view this content.
    will delay opening the spreadsheet by TotalTimeInMinutes, which doesn't make much sense.
    If I'm honest I'm not entirely sure. I'm new to vba and found the above code online.

    All I want to achieve is for the spreadsheet to be closed and saved after 15 minutes of being open, with a 5 minute remaining warning after 10 minutes of being open.

    For some reason, the code seems to remove any toolbar functionality from Excel, which I have diagnosed to be because the security warnings are running in the background as I outlined in my original post. I wasn't sure if another method such as application.ontime would remove this issue, or whether it would still be present, hence asking to see if the vba could be delayed from running by 30 secs or so...

  5. #5
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Delay vba from running upon opening file

    Is anyone able to amend my code in the first post to help with this scenario? many thanks

  6. #6
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Delay vba from running upon opening file

    Try this in the Workbook Module:
    Please Login or Register  to view this content.
    and then this in a standard module:
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Delay vba from running upon opening file

    Thanks for this. The first part seems to work fine, i.e. I get the "This workbook will close in five minutes!" warning after 10 mins.... but then after the 5 minutes pass, I just get the "This workbook will close in five minutes!" warning again, then after another 5 mins I get it again, and again. Instead of the workbook closing?

  8. #8
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Delay vba from running upon opening file

    Try the attached workbook, which works as expected for me. (I changed the minutes to seconds so I wouldn't have to wait so long to test).
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Delay vba from running upon opening file

    Thanks. I think it was because I was pasting the module code in an existing module instead of creating a new one. Not been using VBA long so still learning where to place stuff! Placed in Module2 and appears to work fine - many thanks for your efforts.

  10. #10
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Delay vba from running upon opening file

    I think it was because I was pasting the module code in an existing module instead of creating a new one.
    Don't think that should make any difference, but if it works, it works!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel Delay in Opening
    By romandotcom in forum Excel General
    Replies: 5
    Last Post: 05-12-2014, 12:55 AM
  2. Opening another excel file while macros are running on another
    By mekha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2014, 01:51 AM
  3. Running a macro on opening a file
    By nieironisio in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2008, 10:08 AM
  4. Delay in opening file.
    By coolnax79 in forum Excel General
    Replies: 1
    Last Post: 02-05-2007, 06:37 PM
  5. Delay opening a file from excel shortcut
    By Ameneses in forum Excel General
    Replies: 4
    Last Post: 07-11-2006, 01:35 PM
  6. [SOLVED] Running Macros without opening file.
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-28-2006, 02:56 AM
  7. Running Dialog Sheet when opening Excel File
    By Yacov in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2005, 01:05 PM

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