Unprotect all sheets

[ADK]

How do I unprotect all sheets (all with same password) and then after code
is done, protect all sheets with same password?

 

[Chip Pearson]

Try the following code:

Sub AAA()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Unprotect Password:="abc"
Next WS
'
' your code here
'
For Each WS In ThisWorkbook.Worksheets
WS.Protect Password:="abc"
Next WS
End Sub

You can protect the worksheets with the UserInterfaceOnly flag set to True
which will protect the worksheet against user actions but will allow VBA
code to do whatever it wants. This property is not saved with the workbook,
so you would need to protect the sheets in an Auto_Open procedure, which is
automatically run when the workbook opens.

Sub Auto_Open()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Protect Password:="abc", userinterfaceonly:=True
Next WS
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Try this as a workbook module and a second one with the line

ActiveSheet.Protect Password:="zzz"

to re-protect

Sub stance()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select
ActiveSheet.unProtect Password:="zzz"
Next wSheet
End Sub

Mike

 

[ADK]

So are you saying that the autoopen code below is the only thing needed and
my other code doesn't have to unprotect and protect?

I have this code below which is a assign to a command button. The code is in
sheet1. I can get the code to work for sheet1 (PDSR) but when it selects
sheet2 (CompletionTable) the code fails.

Any ideas?


Private Sub test_Click()
ActiveSheet.Unprotect ("password")
Dim lHiddenRws As Long
With Cells.SpecialCells(xlCellTypeVisible)
lHiddenRws = .Areas(1).Rows.Count + 1
.Areas(1)(lHiddenRws, 1).EntireRow.Hidden = False
Range("A1").CurrentRegion.Rows(Range("A1") _
.CurrentRegion.Rows.Count).Copy Destination:= _
Range("A1").CurrentRegion.Rows(Range("A1").CurrentRegion.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("CompletionTable").Select
ActiveSheet.Unprotect ("password")
Dim lHiddenRwsb As Long
With Cells.SpecialCells(xlCellTypeVisible)
lHiddenRwsb = .Areas(1).Rows.Count + 1
.Areas(1)(lHiddenRwsb, 1).EntireRow.Hidden = False
Range("A1").CurrentRegion.Rows(Range("A1") _
.CurrentRegion.Rows.Count).Copy Destination:= _
Range("A1").CurrentRegion.Rows(Range("A1").CurrentRegion.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub

 

[ADK]

btw I am using 2000 version

So are you saying that the autoopen code below is the only thing needed
and my other code doesn't have to unprotect and protect?

I have this code below which is a assign to a command button. The code is
in sheet1. I can get the code to work for sheet1 (PDSR) but when it
selects sheet2 (CompletionTable) the code fails.

Any ideas?


Private Sub test_Click()
ActiveSheet.Unprotect ("password")
Dim lHiddenRws As Long
With Cells.SpecialCells(xlCellTypeVisible)
lHiddenRws = .Areas(1).Rows.Count + 1
.Areas(1)(lHiddenRws, 1).EntireRow.Hidden = False
Range("A1").CurrentRegion.Rows(Range("A1") _
.CurrentRegion.Rows.Count).Copy Destination:= _
Range("A1").CurrentRegion.Rows(Range("A1").CurrentRegion.Rows.Count +
1)
End With
ActiveSheet.Protect ("password")
Sheets("CompletionTable").Select
ActiveSheet.Unprotect ("password")
Dim lHiddenRwsb As Long
With Cells.SpecialCells(xlCellTypeVisible)
lHiddenRwsb = .Areas(1).Rows.Count + 1
.Areas(1)(lHiddenRwsb, 1).EntireRow.Hidden = False
Range("A1").CurrentRegion.Rows(Range("A1") _
.CurrentRegion.Rows.Count).Copy Destination:= _
Range("A1").CurrentRegion.Rows(Range("A1").CurrentRegion.Rows.Count +
1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub