ExcelBanter - ProtectScan

Unless you did something with the codenames of the worksheets, I bet you're
confusing excel.

Try changing Sheet1 to mySheet1 in both spots.

And you don't need to use that old xlm macro line:

isprotected = sht.ProtectContents _
Or sht.ProtectDrawingObjects _
Or sht.ProtectScenarios

And there shouldn't be a reason to use "Sht.Select" either.

Maybe:

Option Explicit
Sub ProtectScan()
Dim Sht as object 'worksheet, chartsheet, macrosheet, ...
Dim myNote as string
Dim IsProtected as boolean

myNote = ""
For Each Sht In ActiveWorkbook.Sheets
isprotected = sht.ProtectContents _
Or sht.ProtectDrawingObjects _
Or sht.ProtectScenarios
MyNote = MyNote & Sht.Name & ": " & IsProtected & vbLf
Next Sht
MsgBox Prompt:=MyNote, Title:="Sheet Protection On"
End Sub

Pierre wrote:

Wy get a and error
Error 424 - Object requires - on "Workbook.Sheets(Sheet1).Select"

Sub ProtectScan()
Sheet1 = ActiveSheet.Name
MyNote = ""
For Each Sht In ActiveWorkbook.Sheets
Sht.Select
IsProtected = Application.ExecuteExcel4Macro("get.document(7)")
MyNote = MyNote & Sht.Name & ": " & IsProtected & vbCrLf
Next Sht
Workbook.Sheets(Sheet1).Select
MsgBox Prompt:=MyNote, Title:="Sheet Protection On"
End Sub

JP

--

Dave Peterson