Forcing a Workbook to Close after Inactivity

My workbook is not being saved when it gets shutdown, I've looked through the comments and tried to implement a few of the suggestions with no success. How can I use/modify this VBA in order to force save the workbook before closing it?

just a clarification:
this macro will only close the workbook it is saved in correct?

1st routine has a syntax error, should read,

Sub SetTimer()
DownTime = Now + TimeValue("00:30:00")
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=True
End Sub

Thanks for article really helpful!!

what if i want this workbook tosave and go read only instead of closing?

Hello, I copied and pasted the first 3 routines into a standard module but I am getting red letters on this portion.

Application.OnTime EarliestTime:=DownTime, _
Procedure = "ShutDown", Schedule:=True

Any idea why?

This code is great and helpful in many things I do. One problem. In the close event, I call a routine that works great, when 1) run via a button and 2) when the user closes work book. During that process, I need to unprotect the worksheet.

The unprotect doesn't happen when the BeforeClose event is started from the Shutdown routine. Any idea why that would happen?

Personally, I make sure that there are backups either of the original workbook if changes are saved, or of the workbook with the unsaved changes if changes are not otherwise saved. That there is always a way to recover whatever happens.

Dear sir
when i use these codes they show that the compile error invalid outside procedure: Call SetTimer


Please help me or send me the file including this macro.

Without more details, try turning on Designer Mode in the Developer tab. If you can save it with that on, then you've got something in your worksheets before save sub preventing it.

The procedure works fine for me to shut the workbook down but for some reason when using the workbook I am not able to save it manually using the save icon or use any of the items on the menu bar.
Any ideas why ?

Thank you for your answer. I'll test it!

Hi Ron, to save the workbook, just change the .close SaveChanges parameter to True:

Sub ShutDown()
Application.DisplayAlerts = False
ThisWorkbook.Close SaveChanges:=True
End With
End Sub

Make sure you save redundancy backups, as accidental changes can overwrite a whole lot of wotk.

Hello,

perfect script for me. I only have one question. Now it's closing without saving te file, is it possible to change the script in a way that excel will save the file before closing?

Thanks in advance!

Ron

replace the Procedure = "ShutDown",

to Procedure := "ShutDown",


correct code is:

Sub SetTimer()
DownTime = Now + TimeValue("00:00:15")
Application.OnTime EarliestTime:=DownTime, _
Procedure := "ShutDown", Schedule:=True

Rick Swartz 13 Oct 2015, 03:16
@Jordan Champine There is a colon missing in front of the equals sign behind the word procedure.

the colon is missing in the actual article

How would I change the code so that it DOES save the workbook before closing?

Thanks Rick - that did it!

@Donald see my comment from 13. Oct. 2015

When I open the workbook, I get

Compile Error:
Syntax error

On the following piece of code:

Sub SetTimer()
DownTime = Now + TimeValue("00:00:15")
Application.OnTime EarliestTime:=DownTime, _
Procedure = "ShutDown", Schedule:=True

Sharing a workbook would be a simple way around this. However, when you share a workbook you loose several features among them the ability to sort cells by color.

I developed a series of Excel files that my department uses to track our work. One of the key features is the ability to sort by color; without it, a very important feature is lost.

This is not resetting the timer in my case... it executes the macro although doesn't do nothing

@ Barry,

Is there a way to make this macro save the workbook before closing, without having it reopen after the given amount of time (1 hr in this example)?

Whenever I change the code to save before closing, I run into the problem guy faced where the workbook continually reopens, then closes after the given time amount.

Thank you so much for this!

@Guy,

This is because the "Ontime" function is still active until Excel itself is closed.

To deactivate the OnTime function you need to call the Ontime function in the Workbook_BeforeClose event with all the same parameters except the Schedule:=False

When other excel workbooks are open, and I close the workbook of interest, it reopens again after the downtime amount of time. help !

@Jordan Champine There is a colon missing in front of the equals sign behind the word procedure.

I tried the code and get "Compile error: Syntax error"

this portion of code is highlighted in red...

Application.OnTime EarliestTime:=DownTime, _
Procedure = "ShutDown", Schedule:=True

any ideas?

Never mind. I determined that one of the functions was defined in a different module.
I simply changed the function name to Set_time and it worked fine
Thanks

I tried to use your code to force closure of Excel 2010. When I start it I get a debugger error
Compile Error:
Ambiguous name detected: ~

The Call StopTimer is hightlighted in the following

Private Sub Workbook_Open()
Call SetTimer
End Sub

I have worked with macroros before so this is a new one on me.
Thanks