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.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
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.<o></o>
<o></o>
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.<o></o>
<o></o>
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.<o></o>
<o></o>
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. <o></o>
<o></o>
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.<o></o>
<o></o>
<o></o>
In ‘ThisWorkbook’ Excel Object:
<o></o>

Private Sub Workbook_Activate()<o:p></o:p>
    On Error Resume Next<o:p></o:p>
    StartTimer<o:p></o:p>
    With Application<o:p></o:p>
         Sheets("Main").Select<o:p></o:p>
        .DisplayFullScreen = False<o:p></o:p>
        .CommandBars("Worksheet Menu Bar").Enabled = True<o:p></o:p>
    End With<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)<o:p></o:p>
If Timer2Active = True Then Exit Sub<o:p></o:p>
Application.OnTime BootTime, "CloseBook", , False<o:p></o:p>
StartTimer<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
Private Sub Workbook_Deactivate()<o:p></o:p>
    On Error Resume Next<o:p></o:p>
    StartTimer<o:p></o:p>
    With Application<o:p></o:p>
        .DisplayFullScreen = False<o:p></o:p>
        .CommandBars("Worksheet Menu Bar").Enabled = True<o:p></o:p>
    End With<o:p></o:p>
End Sub

<o></o>
<o></o>
In separate module:
<o></o>

Public BootTime As Date<o:p></o:p>
Public Timer2Active As Boolean
<o:p></o:p>
Sub StartTimer()<o:p></o:p>
BootTime = Now + TimeValue("00:00:20")<o:p></o:p>
Application.OnTime BootTime, "CloseBook"<o:p></o:p>
End Sub
<o:p></o:p>
Sub CloseBook()<o:p></o:p>
UserForm1.Show<o:p></o:p>
End Sub
<o:p></o:p>
Sub ReallyCloseBook()<o:p></o:p>
If Timer2Active = False Then Exit Sub<o:p></o:p>
Unload UserForm1<o:p></o:p>
ThisWorkbook.Close False<o:p></o:p>
End Sub

<o></o>
<o></o>
In the UserForm:
<o></o>

Private Sub UserForm_Activate()<o:p></o:p>
Timer2Active = True<o:p></o:p>
Application.OnTime Now + TimeValue("00:00:10"), "ReallyCloseBook"<o:p></o:p>
End Sub
<o:p></o:p>
Private Sub CommandButton1_Click()<o:p></o:p>
Timer2Active = False<o:p></o:p>
StartTimer<o:p></o:p>
Unload UserForm1<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
Private Sub CommandButton2_Click()<o:p></o:p>
Timer2Active = False<o:p></o:p>
ThisWorkbook.Close False<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
Private Sub CommandButton3_Click()<o:p></o:p>
Timer2Active = False<o:p></o:p>
ThisWorkbook.Close True<o:p></o:p>
End Sub<o:p></o:p>

Please help, me and a colleague have spent about 24 hours on this workbook plus one other.

 

[Davius]

Just bumping to see if anyone keep shed any light on this?

 

[Davius]

Shameless bump.

At the moment we have resorted to a simple on time application.close false macro. With a disclaimer that must be accepted by the user before they can access the workbook. Not ideal.