Auto close doesn't quite complete

[picklefactory]

Hi folks. I have a WB that can be opened read only (Password protected to edit) on numerous terminals around our place. I have the following code I use to close it down after 30 mins of inactivity, but it still seems to keep a connection after closing, as when it shuts down via the macro, if I try and open it from another terminal it won't let me in as it shows the previous user as still using it, even though they are not. If they then open any other Excel WB, they then get the password window for this WB, even though it is shut, once they press cancel on that it allows access from elsewhere.
Have I missed something in my code please and maybe not closing it fully?


This section in 'This Workbook'

Dim DownTime As Date

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

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=False
 End Sub
 
 Sub ShutDown()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call StopTimer
    Call SetTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
  ByVal Target As Excel.Range)
    Call StopTimer
    Call SetTimer
End Sub

And this section in a module

Dim DownTime As Date

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

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=False
 End Sub
 
 Sub ShutDown()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

 

[Jimmypop]

Good day

Have you tried

Application.Quit

 

[Jimmypop]

 Sub ShutDown()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
Application.Quit
End Sub

Not sure if will work... Try it.

 

[Colo]

Hi,
In addition to "Application.Quit".
Is that WB placed in a shared network folder on a fileserver?
Open the Computer Management console on your file server (or connect to the server remotely from the management console running on your computer) and go to System Tools -> Shared Folders -> Open files. A list of open files on the current SMB server is displayed on the right side of the window. You can check if the WB process is still open or not.

 

[picklefactory]

Thanks both
I think I have a clash of code in there too. What I didn't post earlier (Didn't think it was relevant.... Doh!) Is that I also have a bit of code that saves the last person to edit the WB. I've just ran a test with "Application.Quit" and changed my timer to 1 minute and the VBA just hung and crashed...... probably due to the bit I didn't post!! I don't think it got as far as Application.Quit, I think the OnTime close can't complete as it can't action the Edit Log code that calls a 'Want to save your changes Msg' which I think might be clashing with the close function, maybe?

The full module is as follows:

Dim DownTime As Date

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

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=False
 End Sub
 
 Sub ShutDown()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
    End Sub

Function LastAuthor()
LastAuthor = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
End Function

Function LastModified() As Date
   LastModified = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

 

[picklefactory]

PS
This is also the full contents of 'This Workbook' too

Private Sub Workbook_Open()
    Call SetTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Worksheets("EDIT LOG").Visible = True
    Worksheets("EDIT LOG").Select
    Range("A2").Select
    Do While Not ActiveCell = ""
    ActiveCell.Offset(1, 0).Select
    Loop
    
ActiveCell.Value = LastAuthor()
ActiveCell.Offset(0, 1).Value = LastModified()
        
    Sheets(Format(Now, "mmmm")).Activate
    
    Worksheets("EDIT LOG").Visible = False
    
    Call StopTimer
    
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call StopTimer
    Call SetTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
  ByVal Target As Excel.Range)
    Call StopTimer
    Call SetTimer
End Sub

 

[picklefactory]

Seems to be working now.
I had a circular error where when the StopTimer function kicked in from the OnTime event it would obviously still look at Workbook before close event which also called StopTimer... not smart by me.
I've run a few tests now and it seems to be working OK....... I think.

 

[picklefactory]

Bugger!!!
I spoke too soon. I now see the issue clearly, if not the solution.
OK, I corrected the error above. Changed the timer to one minute for speed of test. I opened it up on a spare machine next to me and left it for a minute. It all closed down OK with no apparent issue. I then opened it up on my machine, again no issue there so far. On the spare machine next to me, where it had just closed, after a minute or two it has now displayed the password to edit request message again, as if it was trying to open again, even though the WB has previously closed, and now I can't open it from my machine again.
I'm not sure I want to use Application.Quit as we might have more than one WB open, and that will close all of them.
I wonder why Excel brings back the Password to edit msg box some time after the WB has been closed? It doesn't seem to be closing properly and deleting the MS owner file, so when it asks for the password again even though the file was closed, that locks anyone else out

 

[Jimmypop]

Not sure... Have you tried looking at @Colo 's suggestion as well? Not necessary to use Application quit.

 

[picklefactory]

I'm looking at that but not sure I can. the file is in a shared folder on a network drive, but I don't think I can access the management console