Hello all,
I am using the following macro code on a protected unshared workbook used by multiple users across an intranet and am trying to implement 2 additions to the macro code:
1. Disable the startup Autoclose message text box for users who open the file using 'Read Only' or 'Notify'.
2. Change the code so that the ShutDown clock resets itself if the mouse is moved, a new cell is selected, a new worksheet is selected, or a new workbook is selected. Basically, I want to keep the workbook open for any sort of user activity.
The problem I am running into is that the workbook will still autoclose on a user who is simply tabbing between workbooks to view data, or simply using the mouse to select new cells.
'MODULE 1'
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:01:00")
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
'THIS WORKBOOK'
Private Sub Workbook_Open()
MsgBox "This workbook will auto-save and auto-close after 1 minute of inactivity"
Call SetTime
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable
Call SetTime
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call Disable
Call SetTime
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _
As Excel.Range)
Call Disable
Call SetTime
End Sub
Any ideas? Thanks!
Nick
Bookmarks