Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protected sheets | Excel Discussion (Misc queries) | |||
sorting protected sheets | Excel Worksheet Functions | |||
Protected Sheets | Excel Discussion (Misc queries) | |||
Protected sheets | Excel Worksheet Functions | |||
Protected sheets | Excel Worksheet Functions |