protected sheets
 

Hi,
In a workbook with many worksheets, all of which are protected, I was
wondering if it was possible to display (perhaps in A1) a letter or symbol to
indicate if the sheet you're looking at is protected or not. After making
changes to various sheets, I then have to check each one to ensure I have
reset the protection.

Thanks
--
Traa Dy Liooar

Jock

protected sheets
 

Jock,

I don't believe that protecting a sheet fires any of the workbook/worksheet
events. You could try this which will write the protection status of each
worksheet to A1 of that sheet. When you think you've protected each sheet you
can run it again to confirm it.

Sub stance()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password:="Mypass"
ws.Range("A1").Value = "Protected"
ws.Protect Password:="Mypass"
Else
ws.Range("A1").Value = "Unprotected"
flag = flag + 1
End If
Next ws
MsgBox "There are " & flag & " Unprotected worksheets"
End Sub

Mike

"Jock" wrote:

Hi,
In a workbook with many worksheets, all of which are protected, I was
wondering if it was possible to display (perhaps in A1) a letter or symbol to
indicate if the sheet you're looking at is protected or not. After making
changes to various sheets, I then have to check each one to ensure I have
reset the protection.

Thanks
--
Traa Dy Liooar

Jock

protected sheets
 

Good job as usual, Mike.
Thanks
--
Traa Dy Liooar

Jock


"Mike H" wrote:

Jock,

I don't believe that protecting a sheet fires any of the workbook/worksheet
events. You could try this which will write the protection status of each
worksheet to A1 of that sheet. When you think you've protected each sheet you
can run it again to confirm it.

Sub stance()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password:="Mypass"
ws.Range("A1").Value = "Protected"
ws.Protect Password:="Mypass"
Else
ws.Range("A1").Value = "Unprotected"
flag = flag + 1
End If
Next ws
MsgBox "There are " & flag & " Unprotected worksheets"
End Sub

Mike

"Jock" wrote:

Hi,
In a workbook with many worksheets, all of which are protected, I was
wondering if it was possible to display (perhaps in A1) a letter or symbol to
indicate if the sheet you're looking at is protected or not. After making
changes to various sheets, I then have to check each one to ensure I have
reset the protection.

Thanks
--
Traa Dy Liooar

Jock

protected sheets
 

Your welcome Jock and thanks for the feedback.

Mike

"Jock" wrote:

Good job as usual, Mike.
Thanks
--
Traa Dy Liooar

Jock


"Mike H" wrote:

Jock,

I don't believe that protecting a sheet fires any of the workbook/worksheet
events. You could try this which will write the protection status of each
worksheet to A1 of that sheet. When you think you've protected each sheet you
can run it again to confirm it.

Sub stance()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password:="Mypass"
ws.Range("A1").Value = "Protected"
ws.Protect Password:="Mypass"
Else
ws.Range("A1").Value = "Unprotected"
flag = flag + 1
End If
Next ws
MsgBox "There are " & flag & " Unprotected worksheets"
End Sub

Mike

"Jock" wrote:

Hi,
In a workbook with many worksheets, all of which are protected, I was
wondering if it was possible to display (perhaps in A1) a letter or symbol to
indicate if the sheet you're looking at is protected or not. After making
changes to various sheets, I then have to check each one to ensure I have
reset the protection.

Thanks
--
Traa Dy Liooar

Jock