Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting & Unprotecting Sheets
I have a Workbook that contains 6 seperate Sheets. On the PC using this
workbook, there can be 3 other workbooks open at the same time. To cut corners, I use the Sub below to remove protection from all Sheets in the Workbook that contains the 6 seperate sheets, and the following Sub to turn it back on, can you tell me if this is the correct method, or is there a better way. For some reason, I keep getting problems when trying to enter data i unprotected Cells, and was wondering if I am doing this wrong. Thanks Pete Private Sub ProtectionOff() Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("PassWord") Next ws End Sub Private Sub ProtectionOn() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="PassWord" Next ws End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting & Unprotecting Sheets
That is exactly what I use on a 63 sheet workbook. I have assigned keyboard
shortcuts (Ctrl+u, Ctrl+p) to the macros for ease of use. This book has been distributed across my company for 3 years with no problems. Mike F "Pete" wrote in message oups.com... I have a Workbook that contains 6 seperate Sheets. On the PC using this workbook, there can be 3 other workbooks open at the same time. To cut corners, I use the Sub below to remove protection from all Sheets in the Workbook that contains the 6 seperate sheets, and the following Sub to turn it back on, can you tell me if this is the correct method, or is there a better way. For some reason, I keep getting problems when trying to enter data i unprotected Cells, and was wondering if I am doing this wrong. Thanks Pete Private Sub ProtectionOff() Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("PassWord") Next ws End Sub Private Sub ProtectionOn() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="PassWord" Next ws End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting & Unprotecting Sheets
Hi
For some reason one of the Cells in a Protected Sheet was "unmerged", which through out some calculations relying on that cell, any ideas how this can happen on a Protected Sheet. The operator is very inexperienced with Excel, so may have inadvertently done something to unmerge the cells, although I have tried, I cannot achieve this. Pete |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting & Unprotecting Sheets
Your macro works on the activeworkbook. If your 6 sheet workbook isn't the
activeworkbook, then it will not have it's sheets unprotected. You can do (adjust both macros) Private Sub ProtectionOff() Dim ws As Worksheet For Each ws In Workbooks("Mybook.xls").Worksheets ws.Unprotect ("PassWord") Next ws End Sub to specify exactly which workbook. -- Regards, Tom Ogilvy "Pete" wrote in message oups.com... I have a Workbook that contains 6 seperate Sheets. On the PC using this workbook, there can be 3 other workbooks open at the same time. To cut corners, I use the Sub below to remove protection from all Sheets in the Workbook that contains the 6 seperate sheets, and the following Sub to turn it back on, can you tell me if this is the correct method, or is there a better way. For some reason, I keep getting problems when trying to enter data i unprotected Cells, and was wondering if I am doing this wrong. Thanks Pete Private Sub ProtectionOff() Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("PassWord") Next ws End Sub Private Sub ProtectionOn() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="PassWord" Next ws End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting & Unprotecting Sheets
If it just happened once I would consider it a glitch. You shouldn't be able
to unmerge on a protected sheet. If it repeatedly happens with this user, I would troubleshoot the user. Mike F "Pete" wrote in message oups.com... Hi For some reason one of the Cells in a Protected Sheet was "unmerged", which through out some calculations relying on that cell, any ideas how this can happen on a Protected Sheet. The operator is very inexperienced with Excel, so may have inadvertently done something to unmerge the cells, although I have tried, I cannot achieve this. Pete |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting & Unprotecting Sheets
thanks everone, sorted now.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting/Unprotecting all sheets with macro leads to "image" iss | Excel Worksheet Functions | |||
protecting and unprotecting | Excel Worksheet Functions | |||
PROTECTING/UNPROTECTING SHEETS | Excel Discussion (Misc queries) | |||
Protecting/Unprotecting Workbook | Excel Programming | |||
Protecting and Unprotecting multiple sheets | Excel Programming |