Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mass Protect and unProtect
Is there some code that will protect and some more code that will Unprotect
all spreadsheets in a workbook? I thought something like this would work if I named all the sheets, but it doesn't! Copied from Help and change Select to Protect.........Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Protect What's wrong with this code? ("Says subscript out of range") All those sheets exist. In fact this won't even work on 1 sheet! However, I'd rather some code that did all the sheets, even if I added others. Rob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mass Protect and unProtect
Hi Rob,
The following works but depending on just what you want to protect, you might want to set some more parameters for the protect. Sub Test_Protect() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="ossiemac" Next ws End Sub Sub Test_Un_Protect() Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect Password:="ossiemac" Next ws End Sub -- Regards, OssieMac "RobN" wrote: Is there some code that will protect and some more code that will Unprotect all spreadsheets in a workbook? I thought something like this would work if I named all the sheets, but it doesn't! Copied from Help and change Select to Protect.........Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Protect What's wrong with this code? ("Says subscript out of range") All those sheets exist. In fact this won't even work on 1 sheet! However, I'd rather some code that did all the sheets, even if I added others. Rob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mass Protect and unProtect
Thanks OssieMac, just what I needed!!!
Just a matter of interest, any idea why the UnProtect runs MUCH slower? The sheets are mainly filled with charts, Pivot Tables and Pivot Charts & Data. Not too many formulas. There are 23 sheets and takes 5 seconds to UnProtect and about 1/2 sec to Protect. Rob "OssieMac" wrote in message ... Hi Rob, The following works but depending on just what you want to protect, you might want to set some more parameters for the protect. Sub Test_Protect() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="ossiemac" Next ws End Sub Sub Test_Un_Protect() Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect Password:="ossiemac" Next ws End Sub -- Regards, OssieMac "RobN" wrote: Is there some code that will protect and some more code that will Unprotect all spreadsheets in a workbook? I thought something like this would work if I named all the sheets, but it doesn't! Copied from Help and change Select to Protect.........Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Protect What's wrong with this code? ("Says subscript out of range") All those sheets exist. In fact this won't even work on 1 sheet! However, I'd rather some code that did all the sheets, even if I added others. Rob |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mass Protect and unProtect
Hi again Rob,
Other than being facetious and saying that one can run faster going forwards rather than backwards, I really dont know the answer. I think that it would be necessary to have access to the source code and find out what Excel and the macros have to process behind the scenes to answer the question. However, I am pleased that I was able to help you with the original question. -- Regards, OssieMac "RobN" wrote: Thanks OssieMac, just what I needed!!! Just a matter of interest, any idea why the UnProtect runs MUCH slower? The sheets are mainly filled with charts, Pivot Tables and Pivot Charts & Data. Not too many formulas. There are 23 sheets and takes 5 seconds to UnProtect and about 1/2 sec to Protect. Rob "OssieMac" wrote in message ... Hi Rob, The following works but depending on just what you want to protect, you might want to set some more parameters for the protect. Sub Test_Protect() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="ossiemac" Next ws End Sub Sub Test_Un_Protect() Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect Password:="ossiemac" Next ws End Sub -- Regards, OssieMac "RobN" wrote: Is there some code that will protect and some more code that will Unprotect all spreadsheets in a workbook? I thought something like this would work if I named all the sheets, but it doesn't! Copied from Help and change Select to Protect.........Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Protect What's wrong with this code? ("Says subscript out of range") All those sheets exist. In fact this won't even work on 1 sheet! However, I'd rather some code that did all the sheets, even if I added others. Rob |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mass Protect and unProtect
Hi OssieMac,
It was just an interesting observation I made, particularly as there is no other code! I'm just running your code in a couple of modules as stand alones. Anyway, no need to reply, but thanks. Rob "OssieMac" wrote in message ... Hi again Rob, Other than being facetious and saying that one can run faster going forwards rather than backwards, I really don't know the answer. I think that it would be necessary to have access to the source code and find out what Excel and the macros have to process behind the scenes to answer the question. However, I am pleased that I was able to help you with the original question. -- Regards, OssieMac "RobN" wrote: Thanks OssieMac, just what I needed!!! Just a matter of interest, any idea why the UnProtect runs MUCH slower? The sheets are mainly filled with charts, Pivot Tables and Pivot Charts & Data. Not too many formulas. There are 23 sheets and takes 5 seconds to UnProtect and about 1/2 sec to Protect. Rob "OssieMac" wrote in message ... Hi Rob, The following works but depending on just what you want to protect, you might want to set some more parameters for the protect. Sub Test_Protect() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="ossiemac" Next ws End Sub Sub Test_Un_Protect() Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect Password:="ossiemac" Next ws End Sub -- Regards, OssieMac "RobN" wrote: Is there some code that will protect and some more code that will Unprotect all spreadsheets in a workbook? I thought something like this would work if I named all the sheets, but it doesn't! Copied from Help and change Select to Protect.........Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Protect What's wrong with this code? ("Says subscript out of range") All those sheets exist. In fact this won't even work on 1 sheet! However, I'd rather some code that did all the sheets, even if I added others. Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect/ unprotect worksheet | Excel Discussion (Misc queries) | |||
Protect-Unprotect all the sheets | Excel Worksheet Functions | |||
Protect/Unprotect Worksheet | Excel Discussion (Misc queries) | |||
Is there any way that you can protect or unprotect a group of wor. | Excel Discussion (Misc queries) | |||
Protect/unprotect all worksheets | Excel Worksheet Functions |