Autoclosing an idle workbook on a locked workstation. Teething problems.

[Davius]

Currently I have a dashboard style excel document used by multiple users on a network, these users require read/write priviledges and as such only one read/write access file can be opened at a time because of the network.

Once the file is open other users are unable to edit, which is all fine as usally the file is only in use for 5 minutes at a time. The issue that rose it's head is the user that leaves the file open for 'x' amount of time. Sometimes locking their machine and blocking the access for other users to update.

I have trawled online resources and my limited knowledge to come up with VB code to automatically close the file after 'x' minutes of being idle, with a UserForm to notify the user and offer three options 'Continue working' = restarts timer, 'quit and dont save' = as says on the tin and 'save and quit' If no option is clicked after a further 'x' seconds then the user form unloads and the workbook closes without saving.

The issue that I am having trouble with after experimenting with various solutions is that if someone locks their workstation with the read/write file open, the VB code halts after loading the user form and seemly fails to start the second timer until the workstation is unlocked again. Meaning the file stays open and the file cannot be accessed by other users.

Whilst getting to this stage I had some code that was functional in closing the workbook after 'x' time even if the workstation was locked. I'm trying to combine the two to achieve the golden solution, but I'm at a loss after losing the first code.

Below is the functional code as it stands that works perfectly with the workstation unlocked. Can anybody shed any light or help with this problem? Firstly thank you for taking the time to read so far.


In ‘ThisWorkbook’ Excel Object:
Code:

Private Sub Workbook_Activate()
On Error Resume Next
StartTimer
With Application
Sheets("Main").Select
.DisplayFullScreen = False
.CommandBars("Worksheet Menu Bar").Enabled = True
End With
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Timer2Active = True Then Exit Sub
Application.OnTime BootTime, "CloseBook", , False
StartTimer
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
StartTimer
With Application
.DisplayFullScreen = False
.CommandBars("Worksheet Menu Bar").Enabled = True
End With
End Sub




In separate module:
Code:

Public BootTime As Date
Public Timer2Active As Boolean

Sub StartTimer()
BootTime = Now + TimeValue("00:00:20")
Application.OnTime BootTime, "CloseBook"
End Sub

Sub CloseBook()
UserForm1.Show
End Sub

Sub ReallyCloseBook()
If Timer2Active = False Then Exit Sub
Unload UserForm1
ThisWorkbook.Close False
End Sub



In the UserForm:
Code:

Private Sub UserForm_Activate()
Timer2Active = True
Application.OnTime Now + TimeValue("00:00:10"), "ReallyCloseBook"
End Sub

Private Sub CommandButton1_Click()
Timer2Active = False
StartTimer
Unload UserForm1
End Sub

Private Sub CommandButton2_Click()
Timer2Active = False
ThisWorkbook.Close False
End Sub

Private Sub CommandButton3_Click()
Timer2Active = False
ThisWorkbook.Close True
End Sub


Please help, me and a colleague have spent about 24 hours on this workbook plus one other I posted this on another Excel help forum and received no reply, hopefully not because it's too difficult. Wishing for better luck here.

[Davius]

Shameless bump, at the moment we have resorted to a simple on time application.close false with a disclaimer that has to be accepted by the user upon opening. Not ideal.