VBA Code to Close Excel Automatically if in Idle Mode

[dominictagle]

Hi Excel Masters!

This is my problem,
I have a code that can close excel automatically if it is in idle mode, however, the result of the code is not that perfect.. You can't open another excel file if you have opened the excel file with VBA code, in short the window is locked up in the excel with VBA code when this is opened. Another problem on this code is that, it will closed after the 2nd dialog box appear together with another excel files (if you have other excel files opened) and worst, the other excel file are not saved after it closed. Pls help me to fix this problem. I want a VBA code that can allow you to open another excel file and auto saved after the end of specific time if you forgot to save. I am using excel 2010 and XP. Here is my faulty VBA code: Pls consider the two Message Box, I want to retain it coz it is cool.. Thanks in advance!! God Bless! =)

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
TimeInMinutes = 180 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes > 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You have 5 minutes to save before Excel closes."
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit
End Sub

 

[ZabutonNage]

Hi dominictagle,

The code I provided actually does work even though you change the sheet name. But more important, you are not supposed to change it. And there is no need to change it. That's why that worksheet should be hidden. The worksheet may only be used by the code. It is required for storing a value that must be preserved even when code execution stops.

Regarding the message box. Using the standard MsgBox I can't think of a reasonable way to make it work as you are requesting. When you display the MsgBox, code execution will stop. Nothing will happen until a user closes that message box.
You would have to create your own message box using a user form.

Tell me, what is the purpose of it? It really doesn't make sense to me displaying a warning about 10 remaining seconds for the user to save. And when these 10 seconds expire, you make Excel save automatically. The result is the same. In both cases the workbook is saved.
Your message box is just making things complicated.

Try to run the code as explained and let us know if it worked.

 

[RhodEvans]

I've used this code and it works great, however. Now the workbook opens itself back up every 5 minutes ( I changed my timer to 5 mins) . On research I think this may have something to do with the Ontime not being cancelled, but I don't know how to achieve this I have tried:

Application.OnTime dteClose, "scheduledShutdown", , False

but I always get a "Method 'OnTime' of object '_Application failed" message

If it makes any difference I have put this in the workbook_beforeclose event

Anyone understand this OnTime business as obviously I don't quite get it yet.
Your help would be much appreciated.

 

[boboivan]

Hi everybody!

I'm using this code, but it gives the following error:

[COLOR=#ff0000]Private Property Let mdteIdleStartTime(pdteIdleStart As Date)  '' write value to hidden worksheet[/COLOR]    
Application.EnableEvents = False
    [COLOR=#ff0000]InchidereAutomata[/COLOR].Cells(1, 1).Value = pdteIdleStart  '' worksheet's code name might need to be adjusted
    Application.EnableEvents = True
End Property

with the error message:

"Compile error:
Variable not defined"

What do you mean by "write value to hidden worksheet"?
Please advice! Thx!
PS: I use Office 2013 (don't know if it matters) and I have in the workbook protected worksheets with passwords.

 

[boboivan]

Hi everybody!

I'm using this code, but it gives the following error:

[COLOR=#ff0000]Private Property Let mdteIdleStartTime(pdteIdleStart As Date)  '' write value to hidden worksheet[/COLOR]    
Application.EnableEvents = False
    [COLOR=#ff0000]InchidereAutomata[/COLOR].Cells(1, 1).Value = pdteIdleStart  '' worksheet's code name might need to be adjusted
    Application.EnableEvents = True
End Property

with the error message:

"Compile error:
Variable not defined"

What do you mean by "write value to hidden worksheet"?
Please advice! Thx!
PS: I use Office 2013 (don't know if it matters) and I have in the workbook protected worksheets with passwords.

So, I could figured out what was the problem. The code works only if the Date formats in your computer is set (from "Region and Language" option) on English. As I have the settings in other language, the code doesn't run.