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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.