Run macro x minutes after opening workbook

[JugglerJAF]

Is there any way of setting up a macro that is run automatically a specified number of minutes (say 10) after a workbook has been opened.

We have a number of files that are used by many different employees and some people forget to save and close the file before going to a meeting of on lunch, meaning that the file is then not available to other employees.

There is no need for anyone to be in the file for more than 10 minutes so after that time has elapsed I'd like to run a macro to save and close the file (for which I do have the code) - it's just getting it to run 10 minutes after opening the file that I can't figure out!

 

[Richard Schollar]

Hi

Sure is - include the following code in the ThisWorkbook module of the book concerned:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:10:00"), "my_Procedure"
End Sub

In the above, replace my_procedure by the name of the macro you want to run (retain the double quotes though).

Best regards

Richard

 

[HalfAce]

Hello JugglerJAF,
What you want to do is use the OnTime command in vba.
There are other considerations to take into account when invoking this so a simple example of
what you've asked for may not be in your best interest. (Which would look something like this
by the way):

Private Sub Workbook_Open()
Application.OnTime Now + TimeSerial(0, 10, 0), "YourMacroNameHere"
End Sub

I (pretty strongly) recommend doing a search (in vba help and right here on the board) for the
terms: OnTime and TimeSerial to get better aquanted with the ups & downs involved with using
them.


[EDIT:]
Ahh, I see I took too long typing again. Good morning Richard!