Unprotect all sheets
 

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

Unprotect all sheets
 

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)


"ADK" wrote in message
...
How do I unprotect all sheets (all with same password) and then after code
is done, protect all sheets with same password?

Unprotect all sheets
 

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" wrote:

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

Unprotect all sheets
 

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").Current Region.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").Current Region.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub



"Chip Pearson" wrote in message
...
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)


"ADK" wrote in message
...
How do I unprotect all sheets (all with same password) and then after
code is done, protect all sheets with same password?

Unprotect all sheets
 

btw I am using 2000 version


"ADK" wrote in message
...
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").Current Region.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").Current Region.Rows.Count +
1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub



"Chip Pearson" wrote in message
...
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)


"ADK" wrote in message
...
How do I unprotect all sheets (all with same password) and then after
code is done, protect all sheets with same password?