Everyone has some good ideas for you, thought I'd offer just one more and it
kind of goes along with Mike H's second recommendation.
Mine: put a 'rat' in the cell with the suspect and have them tell you who's
unprotecting the sheet!
Caveat: Since this relies on VBA/macro, if your workbook doesn't already
have macros in it, then the presence of this set is going to raise some
eyebrows and perhaps get people looking around.
This is all Workbook event code, so it goes into the ThisWorkbook object's
code module via VB. Just ask if you need to know how to get it to the proper
place in the workbook.
How it works:
It checks when the workbook is opened to see if one specific sheet (one you
specify in the code) is protected or not. It reports that condition, so now
your report knows the state of that sheet's Protect setting. If it was
already unprotected, we don't want to accuse an innocent of unprotecting it.
If it was protected when opened, then as soon as someone unprotects it AND
then selects a cell on that sheet, the fact that the sheet has become
unprotected will be recorded along with the name of the individual logged
into the system. Keep in mind that someone else just might have come along
while the logged in user was away from the desk to do the dastardly deed.
You need to:
add a sheet to the workbook
copy the code below into the ThisWorkbook object's code module and then
set the Const NarcSheetName to the new sheet's name, and
change the name of the Const WatchedSheetName to the name of the sheet that
you want to keep an eye on.
Close the VB Editor and save the workbook.
One other thing you can try to do is to protect the VBProject with a
password. This will help keep them out of your code and keep them from being
able to unhide the reporting sheet. Be sure you write the VBProject's
password down somewhere, it's harder to crack than workbook/worksheet
passwords, but there are tools to get the job done.
To protect the VB Project: while still in the VB Editor, choose
Tools --> VBAProject Properties
then on the [Protection] tab, check the box next to "Lock project for viewing"
and enter and confirm the password for the project and hit the [OK] button.
Actually, it's a good idea for you to keep a copy of the workbook without
the password protection on the VBAProject, just in case you lose its password
later.
TO VIEW the report list, you have to open the workbook, go into the VB
Editor (providing the password along the way), and then select the report
worksheet and set its .Visible property to xlVisible (choice from dropdown
list). Don't worry about hiding it again later, the Workbook_Open event will
take care of that.
At long last, here is the code:
Private SecretReportMade As Boolean
Private Const NarcSheetName = "Sheet3"
Private Const WatchedSheetName = "Sheet1"
Private Sub Workbook_Open()
'test to see if the sheet is unprotected when the
'workbook is opened, this will help prevent
'accusing the innocent of unprotecting it
'if it is unprotected, simply set the
'SecretReportFlag = True to prevent making
'any report later on
'
'make sure that the reporting sheet is invisible
'to anyone without going into the VB Editor
ThisWorkbook.Worksheets(NarcSheetName).Visible = _
xlSheetVeryHidden
SecretReportMade = _
Not ThisWorkbook.Worksheets(WatchedSheetName).ProtectContents
CleanupTattleTaleList
'make row for new entry at top of the list
ThisWorkbook.Worksheets(NarcSheetName).Rows("2:2").Insert _
Shift:=xlShiftDown
If SecretReportMade Then
ThisWorkbook.Worksheets(NarcSheetName).Range("A2") = Now()
ThisWorkbook.Worksheets(NarcSheetName).Range("B2") = _
WatchedSheetName & " Already Unprotected when opened by: " _
& Application.UserName
Else
ThisWorkbook.Worksheets(NarcSheetName).Range("A2") = Now()
ThisWorkbook.Worksheets(NarcSheetName).Range("B2") = _
WatchedSheetName & " Was properly protected when opened by: " _
& Application.UserName
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim WS As Worksheet
If Sh.Name <> WatchedSheetName Or _
SecretReportMade Then
'no work to do, quit
Exit Sub
End If
If Not Sh.ProtectContents Then
'sheet is no longer protected, report who did it!
'
Set WS = ThisWorkbook.Worksheets(NarcSheetName)
'make row for new entry at top of the list
WS.Rows("2:2").Insert Shift:=xlShiftDown
WS.Range("A2") = Now() ' record date/time of the entry
WS.Range("B2") = _
Sh.Name & " Protection removed while in use by: " _
& Application.UserName
SecretReportMade = True
End If
End Sub
Private Sub CleanupTattleTaleList()
'this keeps the 'secret police' sheet from filling up
'and creating an error at some time in the future
'by removing to bottom 500 entries once it gets down
'beyond 2000 entries
Dim lastRow As Long
lastRow = ThisWorkbook.Worksheets(NarcSheetName).Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0).Row
If lastRow > 2000 Then
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(NarcSheetName).Rows("1500:" _
& lastRow).Delete
Application.DisplayAlerts = True
End If
End Sub
