View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
GS[_2_] GS[_2_] is offline
external usenet poster
 
Posts: 3,514
Default Modifying a protect unprotect macro

Here's how I would do it...

Sub Protect_Unprotect()
Const PWORD As String = "password" '//this should be module level
Dim wkSht As Worksheet, statStr As String, vAns As Variant

Application.ScreenUpdating = False
For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & "Sheet " & .Name & vbNewLine
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
vAns = MsgBox("All qualifying permissions?", vbYesNo,
"Protecting Sheets")
If vAns = vbYes Then
wkSht.Protect Password:=PWORD, _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFiltering:=True ', _
AllowFormattingCells:=True, _
AllowDeletingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
Userinterfaceonly:=True, _
AllowDeletingColumns:=True, _
AllowUsingPivotTables:=True
Else
.EnableSelection = xlUnlockedCells
End If
statStr = statStr & ": Protected"
End If
End With
Next
Application.ScreenUpdating = True
MsgBox statStr
End Sub

P.S.: I see your permissions list is commented out same as my sample.
Did you mean to leave that this way?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc