Solved: Unprotecting All Worksheets While Macro RUNS

[BENSON]

Could you please advise me on how to change the following code so that all the worksheets can be unprotected and than protected again once the macro has run through all the sheets

Many Thanks





ActiveSheet.Unprotect Password:="My Password"

ActiveSheet.Protect Password:="My Password", DrawingObjects:=True, Contents:=True, Scenarios:=True

[Simon Lloyd]

HI this macro will unprotect all sheets in a workbook


Sub ShtUnProtect()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
If wSheet.ProtectContents = True Then
wSheet.Unprotect Password:="mypassword"
End If
Next wSheet
End Sub

Regards,
Simon

[Bob Phillips]

Simon,

Oddly enough, you don't have to test whether a sheet is protected to unprotect it



Sub ShtUnProtect()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Unprotect Password:="mypassword"
Next wSheet
End Sub


You would think it would fail, but it doesn't.

[Simon Lloyd]

Your right! (what am i saying...your always right!) but i dont quite understand?, usually if you try to change the state of an object to a state that it already conforms to you usually get a fail!

The code i posted is one that i use but also has an ElseIf after it, guess i need to be more economical with my coding!

Regards,
SImon

[Bob Phillips]

I agree with you, and IMO it should fail, anomalies such as that always worry me, I fear that there are nastier ones out that ther I don't know about : pray2:

[BENSON]

THKS for the code it does unprotect all the worksheets ,but does not reprotect them after the macro has run .Should I just repeat code by changing unprotect to protect?

[Bob Phillips]

Yep, but definitely don't do the test for ProtectContents.

[gnod]

take a look to my post to protect all sheet
http://www.vbaexpress.com/forum/showthread.php?t=10479

to unprotect all worksheet just change sht.Protect password into sht.Unprotect password

'UnProtect all worksheets
Sub UnprotectAllSheet()
Dim sht As Worksheet

Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
sht.Unprotect strPassword
Next sht
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

take a look to my post to protect all sheet
http://www.vbaexpress.com/forum/showthread.php?t=10479

to unprotect all worksheet just change sht.Protect password into sht.Unprotect password

'UnProtect all worksheets
Sub UnprotectAllSheet()
Dim sht As Worksheet

Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
sht.Unprotect strPassword
Next sht
Application.ScreenUpdating = True
End Sub

I think we have alteady been here earlier.