PDA

View Full Version : Solved: Need to know how to Unprotect all sheets at once with a macro



Erays
03-20-2005, 10:13 AM
Need to know how to Unprotect all sheets at once with a macro and then later go back and protect all sheets at once.

Anne Troy
03-20-2005, 10:28 AM
Sub ProtectAllSheets()
Dim s As Worksheet

For Each s In ThisWorkbook.Sheets
s.Select
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
s.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next

End Sub

Sub UnProtectAllSheets()
Dim s As Worksheet

For Each s In ThisWorkbook.Sheets
s.Select
s.UnProtect Password:="test"
Next

End Sub

OH! There's a KB entry here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

Just search the KB first, Erays. :)

Jacob Hilderbrand
03-20-2005, 10:31 AM
No need to select anything.

Sub Unprotect()

Dim ws As Worksheet

For Each ws In Worksheets
ws.Unprotect Password:="MyPassword"
Next

End Sub

Sub Protect()

Dim ws As Worksheet

For Each ws In Worksheets
ws.Protect Password:="MyPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next

End Sub

Erays
03-20-2005, 10:37 AM
How would i just unlock the sheet and then lock just the sheet back not the cells

Jacob Hilderbrand
03-20-2005, 10:42 AM
My code does not change the cells from being locked or not.

Erays
03-20-2005, 11:13 AM
I had to make a change

Sub Unprotect()

Dim ws As Worksheet

For Each ws In Worksheets
' Changed Worksheet.Unprotect Password:="MyPassword"
ws.Unprotect Password:="MyPassword"
Next

End Sub

Sub Protect()

Dim ws As Worksheet

For Each ws In Worksheets
ws.Protect Password:="MyPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next

End Sub

Jacob Hilderbrand
03-20-2005, 11:22 AM
Ok, I mistyped that line in my other reply. Fixed now.