Auto close doesn't quite complete

picklefactory

Well-known Member
Joined
Jan 28, 2005
Messages
508
Office Version
  1. 365
Platform
  1. Windows
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'

VBA Code:
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

VBA Code:
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
VBA Code:
 Sub ShutDown()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
Application.Quit
End Sub

Not sure if will work... Try it.
 
Upvote 0
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.
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
PS
This is also the full contents of 'This Workbook' too

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
Not sure... Have you tried looking at @Colo 's suggestion as well? Not necessary to use Application quit.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top