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,

A major problem of this procedure is that code execution keeps running in the Do While loops preventing the opening of other workbooks and causing some other inconveniences. An approach using Application.OnTime should be more suitable here.

When would you actually like to trigger this procedure?
The code you provided seems to close Excel 180 minutes after being invoked, no matter the user returned to work or not. Is this intended?

The problem of Excel closing without saving other files should be because of Application.DisplayAlerts = False. To avoid workbooks from being closed without prompting to save this should be set to TRUE.
You might also want to consider using ThisWorkbook.Close instead of Application.Quit when other workbooks are open.

Provide some more details.
Will be much easier to find a suitable solution.

 

[tusharm]

1) Use Application.OnTime instead of a CPU intensive loop.

2) If you insist on using MsgBox, how will you close the dialog boxes if no one is at the computer?

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

 

[dominictagle]

Hi dominictagle,

A major problem of this procedure is that code execution keeps running in the Do While loops preventing the opening of other workbooks and causing some other inconveniences. An approach using Application.OnTime should be more suitable here.

When would you actually like to trigger this procedure?
The code you provided seems to close Excel 180 minutes after being invoked, no matter the user returned to work or not. Is this intended?

The problem of Excel closing without saving other files should be because of Application.DisplayAlerts = False. To avoid workbooks from being closed without prompting to save this should be set to TRUE.
You might also want to consider using ThisWorkbook.Close instead of Application.Quit when other workbooks are open.

Provide some more details.
Will be much easier to find a suitable solution.

Hi ZabutonNage!</SPAN>
Thanks for your answer.. Sorry I forgot to replace 180mis to 2 minutes. Considering your suggestions, VBA Code does not functioning. Could you do the VBA code for me with the same idea but functioning.. It would be grateful and highly appreciated. This is a big favor master.. Thanks in advance! J</SPAN></SPAN></SPAN>

 

[dominictagle]

1) Use Application.OnTime instead of a CPU intensive loop.

2) If you insist on using MsgBox, how will you close the dialog boxes if no one is at the computer?

Hi MrExcel MVP,

Where will I put Application.OnTime in my code? what codes to eliminate/change in my VBA code? pls pls help.. thanks!

 

[ZabutonNage]

Hi there,

Here is a solution I came up with. It requires some workbook modification though.
This is what you have to do to make it work. Detailed explanation follows at the bottom.

- create a new worksheet and give it a meaningful code name (I chose wsIdleStart)
- set the worksheet's visibilty to "very hidden" to prevent users from accessing it
- create a standard code module and paste in the code below
- open the ThisWorkbook module and add the code below


The standard code module:

Option Explicit


Private Const miMAX_IDLE_MINUTES As Integer = 2  '' sets the idling time after which the workbook will be closed
Private Const miWARNING_MINUTES  As Integer = 1  '' sets the time before shutdown at which a warning will be given


Private Property Get mdteIdleStartTime() As Date  '' read value from hidden worksheet
    mdteIdleStartTime = wsIdleStart.Cells(1, 1).Value  '' worksheet's code name might need to be adjusted
End Property


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


Public Sub startIdling()


    Dim dteStart As Date
    Dim dteClose As Date
    
    dteStart = Now
    dteClose = dteStart + TimeSerial(0, miMAX_IDLE_MINUTES - miWARNING_MINUTES, 0)
    
    mdteIdleStartTime = dteStart
    
    Application.OnTime dteClose, "'scheduledShutdownWarning #" & dteStart & "#'"
    
End Sub


Public Sub scheduledShutdownWarning(pdteIdleStart As Date)
    If (pdteIdleStart = mdteIdleStartTime) Then
        Beep
        Application.OnTime Now + TimeSerial(0, miWARNING_MINUTES, 0), "'scheduledShutdown #" & mdteIdleStartTime & "#'"
    End If
End Sub


Public Sub scheduledShutdown(pdteIdleStart As Date)
    If (pdteIdleStart = mdteIdleStartTime) Then
        saveAndCloseWorkbook
    End If
End Sub


Private Sub saveAndCloseWorkbook()


    ThisWorkbook.Save
    
    If (Workbooks.Count > 1) Then
        ThisWorkbook.Close
    Else
        Application.Quit
    End If
    
End Sub

The ThisWorkbook module:

Private Sub Workbook_Activate()
    startIdling
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    startIdling
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    startIdling
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    startIdling
End Sub

Okay, so what's going on here?
The startIdling procedure writes the current time and date to the hidden worksheet and schedules the scheduledShutdownWarning procedure while passing the current time. scheduledShutdownWarning confirms whether other idling processes have been scheduled meanwhile. If not, it "beeps" as a warning instead of a message box and schedules scheduledShutdown, which again checks for other scheduled processes and, if none, saves and closes the workbook.

As you probably would like to reset the shutdown timer when users are working on the workbook, take a look at the ThisWorkbook module. Here I chose four events to retrigger the idling process. You might need a different way of triggering for your purposes, but you didn't specify one so I chose some I find useful.

This will actually cause a LOT of schedules when you are working with the workbook. To be honest, I don't know whether this would have any impact on performance and/or resources. If anybody knows, please let me know. During testing I had no problems. (Excel 2010)

You see, I didn't use message boxes. But the situation is just as Tushar said. Who would close the dialogs if nobody is at the computer? Message boxes halt code execution until they are closed. No matter how "cool" you think they are, they are actually in the way of what you want to do.
Why do you need to ask the user to save the workbook if you auto-save it on closing anyway? Instead I offer you a "beep" as a little notice.

Okay, I think this is pretty much what it is about. Let us know if you are having any problems with it.
Bye

 

[dominictagle]

Hi there,

Here is a solution I came up with. It requires some workbook modification though.
This is what you have to do to make it work. Detailed explanation follows at the bottom.

- create a new worksheet and give it a meaningful code name (I chose wsIdleStart)
- set the worksheet's visibilty to "very hidden" to prevent users from accessing it
- create a standard code module and paste in the code below
- open the ThisWorkbook module and add the code below


The standard code module:

Option Explicit


Private Const miMAX_IDLE_MINUTES As Integer = 2  '' sets the idling time after which the workbook will be closed
Private Const miWARNING_MINUTES  As Integer = 1  '' sets the time before shutdown at which a warning will be given


Private Property Get mdteIdleStartTime() As Date  '' read value from hidden worksheet
    mdteIdleStartTime = wsIdleStart.Cells(1, 1).Value  '' worksheet's code name might need to be adjusted
End Property


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


Public Sub startIdling()


    Dim dteStart As Date
    Dim dteClose As Date
    
    dteStart = Now
    dteClose = dteStart + TimeSerial(0, miMAX_IDLE_MINUTES - miWARNING_MINUTES, 0)
    
    mdteIdleStartTime = dteStart
    
    Application.OnTime dteClose, "'scheduledShutdownWarning #" & dteStart & "#'"
    
End Sub


Public Sub scheduledShutdownWarning(pdteIdleStart As Date)
    If (pdteIdleStart = mdteIdleStartTime) Then
        Beep
        Application.OnTime Now + TimeSerial(0, miWARNING_MINUTES, 0), "'scheduledShutdown #" & mdteIdleStartTime & "#'"
    End If
End Sub


Public Sub scheduledShutdown(pdteIdleStart As Date)
    If (pdteIdleStart = mdteIdleStartTime) Then
        saveAndCloseWorkbook
    End If
End Sub


Private Sub saveAndCloseWorkbook()


    ThisWorkbook.Save
    
    If (Workbooks.Count > 1) Then
        ThisWorkbook.Close
    Else
        Application.Quit
    End If
    
End Sub

The ThisWorkbook module:

Private Sub Workbook_Activate()
    startIdling
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    startIdling
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    startIdling
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    startIdling
End Sub

Okay, so what's going on here?
The startIdling procedure writes the current time and date to the hidden worksheet and schedules the scheduledShutdownWarning procedure while passing the current time. scheduledShutdownWarning confirms whether other idling processes have been scheduled meanwhile. If not, it "beeps" as a warning instead of a message box and schedules scheduledShutdown, which again checks for other scheduled processes and, if none, saves and closes the workbook.

As you probably would like to reset the shutdown timer when users are working on the workbook, take a look at the ThisWorkbook module. Here I chose four events to retrigger the idling process. You might need a different way of triggering for your purposes, but you didn't specify one so I chose some I find useful.

This will actually cause a LOT of schedules when you are working with the workbook. To be honest, I don't know whether this would have any impact on performance and/or resources. If anybody knows, please let me know. During testing I had no problems. (Excel 2010)

You see, I didn't use message boxes. But the situation is just as Tushar said. Who would close the dialogs if nobody is at the computer? Message boxes halt code execution until they are closed. No matter how "cool" you think they are, they are actually in the way of what you want to do.
Why do you need to ask the user to save the workbook if you auto-save it on closing anyway? Instead I offer you a "beep" as a little notice.

Okay, I think this is pretty much what it is about. Let us know if you are having any problems with it.
Bye

Hi ZabutoNage,

What do you mean by "code name (I chose wsIdleStart)" .. actually this is the error when i open the worksheet. pls pls explain further... thank you very much...

 

[dominictagle]

Hi ZabutoNage,

What do you mean by "code name (I chose wsIdleStart)" .. actually this is the error when i open the worksheet. pls pls explain further... thank you very much...

Hi ZabutoNage,

Sorry, but couuld you explain to me the first 2 steps. im not good in VBA.
- create a new worksheet and give it a meaningful code name (I chose wsIdleStart)
- set the worksheet's visibilty to "very hidden" to prevent users from accessing it

Thanks a lot!!

 

[ZabutonNage]

Hi there,

I see you're having some difficulties. I'll make it a little easier to set up.

First, create a new worksheet. This shouldn't be a problem.
What is that worksheet called? Could be for example "Sheet4"

Now go to the VBA Editor and open the standard code module of my solution.
Look for all wsIdleStart's and replace them with the name of the worksheet you just created. Should be in two places.

Next we change the worksheet's visibility. This is actually not necessary to make the program work, but it's a good idea to hide the sheet from the user to avoid it being deleted or used in any undesired way.
Take a look at the project explorer. If it is not visible, press Ctrl+R or select it from the View menu.
Look for the worksheet you have created. Select it and press F4. This will open the properties window for that worksheet. It contains a poperty called Visible. Change it to 2 - xlSheetVeryHidden.

Any problems with the rest of the code?

 

[dominictagle]

Hi there,

I see you're having some difficulties. I'll make it a little easier to set up.

First, create a new worksheet. This shouldn't be a problem.
What is that worksheet called? Could be for example "Sheet4"

Now go to the VBA Editor and open the standard code module of my solution.
Look for all wsIdleStart's and replace them with the name of the worksheet you just created. Should be in two places.

Next we change the worksheet's visibility. This is actually not necessary to make the program work, but it's a good idea to hide the sheet from the user to avoid it being deleted or used in any undesired way.
Take a look at the project explorer. If it is not visible, press Ctrl+R or select it from the View menu.
Look for the worksheet you have created. Select it and press F4. This will open the properties window for that worksheet. It contains a poperty called Visible. Change it to 2 - xlSheetVeryHidden.

Any problems with the rest of the code?

Hi ZabutonNage,
thanks for your untiring support. I just realized, the problem on this code is that if I change the sheet name, the code will not function.. coz every month i change the name of the sheet. i know this is to much, but can you give me a code that functioning even i changed the sheet name, and if possible i need a msg box that says "You have 10 seconds to save." and if ignore within another 10 sec. the excel will autosave and close.. hope you can give me appropriate code coz im not good in this... thanks in advance!!! cheers!