-1

After a new file has been open for 5 minutes, I want to ask the user if they want to save the file.

I found the code to ask them if they want to save (below), I just do not know how to modify to

  1. Have the macro run on every new workbook
  2. Have the prompt display after 5 minutes.

Code

If Not ThisWorkbook.Saved Then 
    If MsgBox("Do you want to save the file before continuing?",vbYesNo,"Save?") = vbYes Then
        ThisWorkbook.Save
    End If
End If
Community
  • 1
  • 1
Dawn
  • 1

1 Answers1

0

You want to add a timer to the workbook. See this thread: VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds

E.g.

alertTime = Now + TimeValue("00:05:00")'Adds 5 minutes
Application.OnTime alertTime, "YOUR MACRO SUB"

To summarise:

  1. Add a sub e.g. on Workbook open event that runs the timer running a separate Sub

  2. In the separate Sub add the messagebox and the run the initial sub

Community
  • 1
  • 1
AnalystCave.com
  • 4,884
  • 2
  • 22
  • 30
  • Awesome - thank you! Here is my next issue - I need to have this macro run on every new workbook on my boss' computer. I am not sure how/where to save this to ensure it launches. Thank you!! – Dawn Aug 06 '14 at 20:55
  • Search the topic of Excel Add-ins. Excel Add-ins places in the "C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns" are files that will open together with every Excel workbook on the workstation. In the Add-in add your macro to the Private Sub Workbook_Open() method but reference the "Activeworkbook" not the current workbook. Here is my fav tutorial on Add-ins http://www.ozgrid.com/VBA/excel-add-in-create.htm – AnalystCave.com Aug 06 '14 at 21:43
  • Well I gave it my best shot and after an hour I could not get it to work. – Dawn Aug 07 '14 at 20:16