Unprotect Macro

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Is there a little simple macro that I could create to
unprotect all of the worksheets in a workbook at the same
time?

I am somewhat new to programming and I cant figure it out.

TIA

Scott
 
Hi Scott!

Sub UnprotectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Unprotect Password:="not4u2see"
Next n
End Sub

And if you want fries with that:

Sub ProtectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Protect Password:="not4u2see"
Next n
End Sub

Or just go for the combo:

Public Sub ToggleProtect1()
' From J E McGimpsey modified by NH
Application.ScreenUpdating = False
Const PWORD As String = "not4u2see"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
Application.ScreenUpdating = True
MsgBox Mid(statStr, 2)
End Sub

All of the above are best put in your Personal.xls file so that they
are available for all workbooks.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Worked beautifully! Thank you!
-----Original Message-----
Hi Scott!

Sub UnprotectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Unprotect Password:="not4u2see"
Next n
End Sub

And if you want fries with that:

Sub ProtectAllSheets()
Dim n As Integer
For n = 1 To Worksheets.Count
Worksheets(n).Protect Password:="not4u2see"
Next n
End Sub

Or just go for the combo:

Public Sub ToggleProtect1()
' From J E McGimpsey modified by NH
Application.ScreenUpdating = False
Const PWORD As String = "not4u2see"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
Application.ScreenUpdating = True
MsgBox Mid(statStr, 2)
End Sub

All of the above are best put in your Personal.xls file so that they
are available for all workbooks.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.



.
Click to expand...
 
Hi Scott!

Thanks for thanks and confirmation that it works OK; it's always
appreciated and useful for Google searchers.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top