Try this macro. It checks if the active sheet is protected. If it is, it
examines every cell in the UsedRange. Every cell that is locked is listed on
a new sheet which is added at the end of the workbook.
Option Explicit
Sub AuditShtProtection()
'Declare local variables.
Dim x As Long, c As Range, y As Long, z As Long
Dim StartSht As Worksheet, HitCount As Long
Dim NuSht As Worksheet
'Remember the starting sheet.
Set StartSht = ActiveSheet
If ActiveSheet.ProtectContents = True Then
'Add a new worksheet to the current workbook at the end.
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NuSht = ActiveSheet
HitCount& = 1
DoEvents
StartSht.Activate
For Each c In ActiveSheet.UsedRange
If c.Locked = True Then
'List the cell's sheet name, address, and formula on NuSht.
HitCount& = HitCount& + 1
NuSht.Cells(HitCount&, 1).Value = "'" & ActiveSheet.Name
NuSht.Cells(HitCount&, 2).Value = "'" & c.Address
NuSht.Cells(HitCount&, 3).Value = "'" & c.Formula
End If
Next c
Else
MsgBox StartSht.Name & " is not protected", , "AuditShtProtection"
Exit Sub
End If
If HitCount& = 1 Then
MsgBox "No locked cells were found were found", , "AuditShtProtection"
Application.DisplayAlerts = False
NuSht.Delete
Application.DisplayAlerts = True
Else
'Add headings on NuSht
NuSht.Cells(1, 1).Value = "Sheet"
NuSht.Cells(1, 2).Value = "Cell"
NuSht.Cells(1, 3).Value = "Formula"
NuSht.Activate
NuSht.Cells.Select
NuSht.Cells.EntireColumn.AutoFit
End If
cleanup:
'Free object variables.
Set StartSht = Nothing
Set NuSht = Nothing
Set c = Nothing
MsgBox "Done!"
End Sub
Put the macro code in a general VBA module in your workbook (it doesn't have
to be the same workbook you want to audit). If you are new to macros, this
link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/
Hope this helps,
Hutch
