Need a Notification/Alarm when a file is left open for too long

[JustAGuy5]

Hi There,

I have a problem that I'm sure many folks have encountered. Our office utilizes a shared drive to access documents. It has its pros and cons, but we've recently run into a problem. There is a coworker who keeps forgetting to close an important excel document that is often used by multiple staff members. No one really needs to spend any significant amount of time in it, we just need to look at it, make any changes if necessary, and then close it. We really don't need to be in it for any longer than 5 - 10 minutes at a time. Unfortunately, this other staff member will lock us out for a day or more because he keeps forgetting to close the file and then goes home or leaves his office for a meeting elsewhere. It's causing problems. Is there any way for the file to alarm or have some sort of notification when the it has remained opened after a determined period of time?

I looked through the forums and I couldn't seem to find anything. I'm sure that other people have encountered similar experiences! Any help provided will be greatly appreciated!!!

 

[Logit]

.
This macro will close workbook after time period of inactivity. You set the time of inactivity in the macro at top.

So long as someone is actively typing, changing sheets, etc. the timer will reset itself.


This goes in a module :

Option Explicit


Const idleTime = 60 'seconds
Dim Start
Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
'///////////////////////////////////////////////////////
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
       
    'Application.DisplayAlerts = True
'//////////////////////////////////////////////////////////
    'Application.DisplayAlerts = False
    Application.Quit
    ActiveWorkbook.Close SaveChanges:=True
    
    Application.DisplayAlerts = True
End Sub

Start the macro from the ThisWorkbook ... Workbook_Open event.

Option Explicit


Private Sub Workbook_Open()
    StartTimer
End Sub


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
StartTimer
End Sub


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

 

[JustAGuy5]

.
This macro will close workbook after time period of inactivity. You set the time of inactivity in the macro at top.

So long as someone is actively typing, changing sheets, etc. the timer will reset itself.


This goes in a module :

Option Explicit


Const idleTime = 60 'seconds
Dim Start
Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
'///////////////////////////////////////////////////////
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
       
    'Application.DisplayAlerts = True
'//////////////////////////////////////////////////////////
    'Application.DisplayAlerts = False
    Application.Quit
    ActiveWorkbook.Close SaveChanges:=True
    
    Application.DisplayAlerts = True
End Sub

Start the macro from the ThisWorkbook ... Workbook_Open event.

Option Explicit


Private Sub Workbook_Open()
    StartTimer
End Sub


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
StartTimer
End Sub


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

Hi Logit,

Thanks so much for responding!!! I'm not that advanced at using macros. This is what I did, I copied the first code and then I right clicked on a sheet, selected view code, double clicked on a sheet, and pasted the code in. I then repeated that step for each sheet within the workbook. Once finished, I hit save and closed the window. Then I hit Alt + F11. Double clicked on "This Workbook" and pasted the second code you provide. I then saved it and closed the window. Once that was done, I saved the file and closed it.

When I reopened the file to see if it worked, an the VBA opened immediately with the following error, "Compile Error: Sub or Function not defined" I hit okay, and then it immediately opened the workbook page and highlighted in yellow was the "Private Sub Workbook_Open()" portion of the code. I tried inserting a time within the parentheses like I saw elsewhere ""60 'seconds", but that didn't seem to work. I clearly did something wrong, but I'm not sure where. Any help will be greatly appreciated!!!

If I end up getting this to work. Will it save any changes made prior to closing the file?

 

[Logit]

.
In my Post #2 ... To paste the first macro into a Module ... go to the MENU BAR at top of workbook. Select DEVELOPER tab. Then click on VISUAL BASIC at far left.

This opens the VBE (Visual Basic Editor). At the top of the window, in the MENU BAR of the VBE, click on INSERT / MODULE.

This creates a module in your workbook, and the large white colored window on the right is where you will paste the first macro.


Now, look at the left side of the VBE. Double-click on THISWORKBOOK. You will paste the second macro in the large white colored window on the right

Save your workbook, close it and re-open it. If you haven't changed the time setting in the first macro that was pasted into the MODUEL, the workbook will
automatically close after one minute of inactivity. You can change the time limit to anything you want ... in seconds. Five minutes would be 300 seconds.
Ten minutes would be 600 seconds, etc.

Prior to auto-closing the workbook, it will automatically save any changes that had been made prior to closing.

ps: No need to quote previous posting when you respond. It takes up needed space on the servers.

 

[JustAGuy5]

Hi Logit,

Thank you so much for all of your help! It did work as you described; however, I think I may have done something wrong because I noticed a couple of odd things when testing it. I found that the file with the macro in it wouldn’t open. When I tried to open it, the only thing I saw was the green Excel launch window. It showed the file name loading at 100%, but it never seemed to actually launch. Furthermore, after about a minute of it being hung up on the launch screen, it closed on its own. I think that may have something to do with the 60 second auto-close macro. I did find that the file opened immediately, without hesitation, if another Excel file was open, but it wouldn’t open if there wasn’t another Excel file open.


Once I got the file to open (with another file already opened), I let it sit there to see how it worked. After about 60 seconds of inactivity, the file closed as expected, but it also closed every other Excel document I had open. I tested it with up to 4 other Excel files, and it closed all of them. Did I do something wrong?

PS. I won't quote anyone anymore. Thanks for the heads up!

 

[Logit]

.
Slight edit to the code should fix that (close only the AUTO CLOSE workbook) :

Option Explicit


Const idleTime = 60 'seconds
Dim Start
Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
     
'///////////////////////////////////////////////////////
    Application.DisplayAlerts = False
   
    Application.ActiveWorkbook.Close SaveChanges:=True
    
    Application.DisplayAlerts = True
End Sub

If this AUTO CLOSE workbook is the only workbook running, when it closes you will be left with an empty workbook on screen, because the command for APPLICATION.QUIT was not included. Excel will
be running but no worksheets displayed.

Try the above code and run only the Auto Close workbook to see what I mean.

I don't have an answer why the workbook is having difficulty loading at your end.