Protect sheet on idle

[lynnnow]

Hi,

I've got an Excel sheet that needs to be protected after a period of inactivity.

I've the protection part correct, I'm struggling with getting the Inactivity part sorted out.

The parts i've got so far are pretty simple and they are as follows:


Sub SheetProtect()
Application.OnTime Now + TimeValue("00:00:05"), "ProtSheet"
End Sub

Sub ProtSheet()
ActiveSheet.Protect "protsheet", True, True, True
MsgBox "Sheet is protected", vbCritical, "...::: Lynx's Corner :::..."
End Sub

Sub UnProtectSheet()
ActiveSheet.Unprotect
'SheetProtect
End Sub


What I found in the help file is something like KeyPress event but that was shown for a userform. I'm stuck on the inactivity part.

Any clues on how to get this done?

Lynnnow:dunno

[johnske]

Hi Lynnow,

Depends exactly what you mean by 'inactivity'... probably best to use one of the worksheet events to do this - here's a few ideas for the relevant Worksheet module for you to start with


Option Explicit
Private Sub Worksheet_Calculate()
Application.OnTime Now + TimeValue("00:00:05"), "ProtSheet"
End Sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.OnTime Now + TimeValue("00:00:05"), "ProtSheet"
End Sub

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnTime Now + TimeValue("00:00:05"), "ProtSheet"
End Sub

Option Explicit
Private Sub Worksheet_Activate()
Application.OnTime Now + TimeValue("00:00:05"), "ProtSheet"
End Sub


HTH,
John

[Bob Phillips]

There is no built-in facility for measuring inactivity, you would need to check every applicable event, and set a new timer to call a routine to protect your sheet.

But, and these are important, clean out the old timer otherwise the originally scheduled action will happen, and you should do it at workbook level so all sheets are covered.

[Bob Phillips]

Here is sample code


Private Const nElapse As Long = 5
Private Const pzProc As String = "ProtectSheet"
Private nTime As Date

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
SetProtectTime
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
SetProtectTimer
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
SetProtectTimer
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
SetProtectTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
SetProtectTimer
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
SetProtectTimer
End Sub

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
SetProtectTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
SetProtectTimer
End Sub

Private Sub SetProtectTimer()
Application.OnTime nTime, pzProc,Schedule:=False
nTime = Now + TimeSerial(0, 0, nElapse)
Application.OnTime nTime, pzProc
End Sub