M
Mike The Newb
I apologize in advance for the length of this request/thread but I wanted to
show the history.
Paul B and Bob Phillips assisted me with an awesome code to kick out
inactive users within a given time period and allow certain named users to be
exempt/excluded from the kick out. Unfortunately, some non excluded users
have experienced an odd occurrence after the code closes the file on them for
inactivity - the file reopens itself, saves itself and closes itself -
sometimes continuously, within the code's named timed interval (20 seconds in
test mode).
Past thread as follows:
This oddity occurred for two different non exempt users. For one, it
occurred continuously every 20 seconds until he got out of Excel and then
back into it without opening the test file again. For another user, it only
occurred once and cannot be initiated again by repeating the events. In both
cases the users had another unrelated Excel file open as well.
Here's the code Workbook wise:
Private Sub Workbook_Open()
Select Case Environ("Username")
Case "hks0679"
Case Else:
MsgBox "This workbook will auto-close after 20 seconds of
inactivity"
Call SetTime
End Select
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Select Case Environ("Username")
Case "hks0679"
Case Else:
Call Disable
Call SetTime
End Select
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Select Case Environ("Username")
Case "hks0679"
Case Else:
Call Disable
Call SetTime
End Select
End Sub
Module wise:
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:00:20")
Application.OnTime DownTime, "ShutDown"
End Sub
Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown",
Schedule:=False
End Sub
It's a random occurrence, at least I cannot cause it to happen by repeating
the initial steps or trying other step combinations in terms of the sequence
of opening files, when they're manually saved by users (possibly before 20
seconds), etc. When user hks0679 opens it, she receives no message regarding
the auto close in 20 seconds, the file doesn’t save and close itself in 20
seconds (those aspects work great) and she has no issues with it opening
itself after it has been closed out.
Any insight would be greatly appreciated. This is an awesome code and it has
a lot potential for a variety of files used daily by multiple users.
Regards,
Mike
show the history.
Paul B and Bob Phillips assisted me with an awesome code to kick out
inactive users within a given time period and allow certain named users to be
exempt/excluded from the kick out. Unfortunately, some non excluded users
have experienced an odd occurrence after the code closes the file on them for
inactivity - the file reopens itself, saves itself and closes itself -
sometimes continuously, within the code's named timed interval (20 seconds in
test mode).
Past thread as follows:
This oddity occurred for two different non exempt users. For one, it
occurred continuously every 20 seconds until he got out of Excel and then
back into it without opening the test file again. For another user, it only
occurred once and cannot be initiated again by repeating the events. In both
cases the users had another unrelated Excel file open as well.
Here's the code Workbook wise:
Private Sub Workbook_Open()
Select Case Environ("Username")
Case "hks0679"
Case Else:
MsgBox "This workbook will auto-close after 20 seconds of
inactivity"
Call SetTime
End Select
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Select Case Environ("Username")
Case "hks0679"
Case Else:
Call Disable
Call SetTime
End Select
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Select Case Environ("Username")
Case "hks0679"
Case Else:
Call Disable
Call SetTime
End Select
End Sub
Module wise:
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:00:20")
Application.OnTime DownTime, "ShutDown"
End Sub
Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown",
Schedule:=False
End Sub
It's a random occurrence, at least I cannot cause it to happen by repeating
the initial steps or trying other step combinations in terms of the sequence
of opening files, when they're manually saved by users (possibly before 20
seconds), etc. When user hks0679 opens it, she receives no message regarding
the auto close in 20 seconds, the file doesn’t save and close itself in 20
seconds (those aspects work great) and she has no issues with it opening
itself after it has been closed out.
Any insight would be greatly appreciated. This is an awesome code and it has
a lot potential for a variety of files used daily by multiple users.
Regards,
Mike