Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
I set up the following, attached to Icons on personal tool bar
( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
See if you can fit this into your need...
================================= Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub -- steveB Remove "AYN" from email to respond "John F." <John wrote in message ... I set up the following, attached to Icons on personal tool bar ( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
Thanks Steve Bell;
I gave it a good try, but am getting stuck. The way I'm trying to put this together with your sample, I get an error message at "next ws". Its looking for another "For". Any ideas? "STEVE BELL" wrote: See if you can fit this into your need... ================================= Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub -- steveB Remove "AYN" from email to respond "John F." <John wrote in message ... I set up the following, attached to Icons on personal tool bar ( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
You should be able to use the code as is, without the use of Next ws - just
use Next Make sure that you have Option Explicit at the top of the module. You may not have closed all your loops (For....Next, If....End If). Excel will help you find them.... another approach is to loop through the sheets by number Dim x as Integer For x = 1 to ActiveWorkbook.Worksheets.Count If Sheets(x).ProtectContents = True Then Sheets(x).Unprotect MsgBox "unProtect Sheet(s)" Else Sheets(x).Protect MsgBox "Protect Sheet(s)" End If Next ================================== Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub ============================= -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Thanks Steve Bell; I gave it a good try, but am getting stuck. The way I'm trying to put this together with your sample, I get an error message at "next ws". Its looking for another "For". Any ideas? "STEVE BELL" wrote: See if you can fit this into your need... ================================= Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub -- steveB Remove "AYN" from email to respond "John F." <John wrote in message ... I set up the following, attached to Icons on personal tool bar ( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
Steve:
Lets try to simplify and clarify this a bit. A: Forget the message box idea. I obviously didn't make my question clear at all. B: I don't need to have the macro unprotect the protected and protect the unprotected sheets. They are all to be unprotected. My concern was that if any one of the sheets WAS protected and then I run the macro to protect every sheet, that I might get a glitch at some point (actually, I am). Therefore, I'm trying to Protect each sheet. IF a sheet is already in Protect mode, Pass on to the next sheet ............... The following is my latest attempt. I'm still getting message that it wants a "FOR". I don't know what is wrong with the "FOR" that is there already. I presume that the IF-Then_Else Statement is messing it up, somehow. Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = True Then ws.Unprotect ("123") Else If ws.ProtectContents = False Then Next ws ActiveWorkbook.Unprotect (["456"]) Application.ScreenUpdating = True End Sub Thanks John F. "STEVE BELL" wrote: You should be able to use the code as is, without the use of Next ws - just use Next Make sure that you have Option Explicit at the top of the module. You may not have closed all your loops (For....Next, If....End If). Excel will help you find them.... another approach is to loop through the sheets by number Dim x as Integer For x = 1 to ActiveWorkbook.Worksheets.Count If Sheets(x).ProtectContents = True Then Sheets(x).Unprotect MsgBox "unProtect Sheet(s)" Else Sheets(x).Protect MsgBox "Protect Sheet(s)" End If Next ================================== Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub ============================= -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Thanks Steve Bell; I gave it a good try, but am getting stuck. The way I'm trying to put this together with your sample, I get an error message at "next ws". Its looking for another "For". Any ideas? "STEVE BELL" wrote: See if you can fit this into your need... ================================= Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub -- steveB Remove "AYN" from email to respond "John F." <John wrote in message ... I set up the following, attached to Icons on personal tool bar ( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
Actually the code is just general to do it all.
The message box is just a signal and can be removed or replaced with anything. To just protect: =============================== Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = False Then ws.Protect ("123") End If Next Application.ScreenUpdating = True ================================== -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Steve: Lets try to simplify and clarify this a bit. A: Forget the message box idea. I obviously didn't make my question clear at all. B: I don't need to have the macro unprotect the protected and protect the unprotected sheets. They are all to be unprotected. My concern was that if any one of the sheets WAS protected and then I run the macro to protect every sheet, that I might get a glitch at some point (actually, I am). Therefore, I'm trying to Protect each sheet. IF a sheet is already in Protect mode, Pass on to the next sheet .............. The following is my latest attempt. I'm still getting message that it wants a "FOR". I don't know what is wrong with the "FOR" that is there already. I presume that the IF-Then_Else Statement is messing it up, somehow. Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = True Then ws.Unprotect ("123") Else If ws.ProtectContents = False Then Next ws ActiveWorkbook.Unprotect (["456"]) Application.ScreenUpdating = True End Sub Thanks John F. "STEVE BELL" wrote: You should be able to use the code as is, without the use of Next ws - just use Next Make sure that you have Option Explicit at the top of the module. You may not have closed all your loops (For....Next, If....End If). Excel will help you find them.... another approach is to loop through the sheets by number Dim x as Integer For x = 1 to ActiveWorkbook.Worksheets.Count If Sheets(x).ProtectContents = True Then Sheets(x).Unprotect MsgBox "unProtect Sheet(s)" Else Sheets(x).Protect MsgBox "Protect Sheet(s)" End If Next ================================== Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub ============================= -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Thanks Steve Bell; I gave it a good try, but am getting stuck. The way I'm trying to put this together with your sample, I get an error message at "next ws". Its looking for another "For". Any ideas? "STEVE BELL" wrote: See if you can fit this into your need... ================================= Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub -- steveB Remove "AYN" from email to respond "John F." <John wrote in message ... I set up the following, attached to Icons on personal tool bar ( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
Steve:
Works Great!! Thanks ever so much!! So simple when you know what you're doing, eh? Thanks again John F. "STEVE BELL" wrote: Actually the code is just general to do it all. The message box is just a signal and can be removed or replaced with anything. To just protect: =============================== Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = False Then ws.Protect ("123") End If Next Application.ScreenUpdating = True ================================== -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Steve: Lets try to simplify and clarify this a bit. A: Forget the message box idea. I obviously didn't make my question clear at all. B: I don't need to have the macro unprotect the protected and protect the unprotected sheets. They are all to be unprotected. My concern was that if any one of the sheets WAS protected and then I run the macro to protect every sheet, that I might get a glitch at some point (actually, I am). Therefore, I'm trying to Protect each sheet. IF a sheet is already in Protect mode, Pass on to the next sheet .............. The following is my latest attempt. I'm still getting message that it wants a "FOR". I don't know what is wrong with the "FOR" that is there already. I presume that the IF-Then_Else Statement is messing it up, somehow. Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = True Then ws.Unprotect ("123") Else If ws.ProtectContents = False Then Next ws ActiveWorkbook.Unprotect (["456"]) Application.ScreenUpdating = True End Sub Thanks John F. "STEVE BELL" wrote: You should be able to use the code as is, without the use of Next ws - just use Next Make sure that you have Option Explicit at the top of the module. You may not have closed all your loops (For....Next, If....End If). Excel will help you find them.... another approach is to loop through the sheets by number Dim x as Integer For x = 1 to ActiveWorkbook.Worksheets.Count If Sheets(x).ProtectContents = True Then Sheets(x).Unprotect MsgBox "unProtect Sheet(s)" Else Sheets(x).Protect MsgBox "Protect Sheet(s)" End If Next ================================== Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub ============================= -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Thanks Steve Bell; I gave it a good try, but am getting stuck. The way I'm trying to put this together with your sample, I get an error message at "next ws". Its looking for another "For". Any ideas? "STEVE BELL" wrote: See if you can fit this into your need... ================================= Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub -- steveB Remove "AYN" from email to respond "John F." <John wrote in message ... I set up the following, attached to Icons on personal tool bar ( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
Glad to help...
Actually the credit goes to this ng. This is just one of many I learned here... Go to this site and download the add-in. It's a great tool to get answers quickly... http://www.rondebruin.nl/Google.htm -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Steve: Works Great!! Thanks ever so much!! So simple when you know what you're doing, eh? Thanks again John F. "STEVE BELL" wrote: Actually the code is just general to do it all. The message box is just a signal and can be removed or replaced with anything. To just protect: =============================== Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = False Then ws.Protect ("123") End If Next Application.ScreenUpdating = True ================================== -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Steve: Lets try to simplify and clarify this a bit. A: Forget the message box idea. I obviously didn't make my question clear at all. B: I don't need to have the macro unprotect the protected and protect the unprotected sheets. They are all to be unprotected. My concern was that if any one of the sheets WAS protected and then I run the macro to protect every sheet, that I might get a glitch at some point (actually, I am). Therefore, I'm trying to Protect each sheet. IF a sheet is already in Protect mode, Pass on to the next sheet .............. The following is my latest attempt. I'm still getting message that it wants a "FOR". I don't know what is wrong with the "FOR" that is there already. I presume that the IF-Then_Else Statement is messing it up, somehow. Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = True Then ws.Unprotect ("123") Else If ws.ProtectContents = False Then Next ws ActiveWorkbook.Unprotect (["456"]) Application.ScreenUpdating = True End Sub Thanks John F. "STEVE BELL" wrote: You should be able to use the code as is, without the use of Next ws - just use Next Make sure that you have Option Explicit at the top of the module. You may not have closed all your loops (For....Next, If....End If). Excel will help you find them.... another approach is to loop through the sheets by number Dim x as Integer For x = 1 to ActiveWorkbook.Worksheets.Count If Sheets(x).ProtectContents = True Then Sheets(x).Unprotect MsgBox "unProtect Sheet(s)" Else Sheets(x).Protect MsgBox "Protect Sheet(s)" End If Next ================================== Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub ============================= -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Thanks Steve Bell; I gave it a good try, but am getting stuck. The way I'm trying to put this together with your sample, I get an error message at "next ws". Its looking for another "For". Any ideas? "STEVE BELL" wrote: See if you can fit this into your need... ================================= Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub -- steveB Remove "AYN" from email to respond "John F." <John wrote in message ... I set up the following, attached to Icons on personal tool bar ( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fancier Protect Sheets, Workbooks
Thanks Steve, this was just what I needed. Brilliant.
"STEVE BELL" wrote: Actually the code is just general to do it all. The message box is just a signal and can be removed or replaced with anything. To just protect: =============================== Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = False Then ws.Protect ("123") End If Next Application.ScreenUpdating = True ================================== -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Steve: Lets try to simplify and clarify this a bit. A: Forget the message box idea. I obviously didn't make my question clear at all. B: I don't need to have the macro unprotect the protected and protect the unprotected sheets. They are all to be unprotected. My concern was that if any one of the sheets WAS protected and then I run the macro to protect every sheet, that I might get a glitch at some point (actually, I am). Therefore, I'm trying to Protect each sheet. IF a sheet is already in Protect mode, Pass on to the next sheet .............. The following is my latest attempt. I'm still getting message that it wants a "FOR". I don't know what is wrong with the "FOR" that is there already. I presume that the IF-Then_Else Statement is messing it up, somehow. Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = True Then ws.Unprotect ("123") Else If ws.ProtectContents = False Then Next ws ActiveWorkbook.Unprotect (["456"]) Application.ScreenUpdating = True End Sub Thanks John F. "STEVE BELL" wrote: You should be able to use the code as is, without the use of Next ws - just use Next Make sure that you have Option Explicit at the top of the module. You may not have closed all your loops (For....Next, If....End If). Excel will help you find them.... another approach is to loop through the sheets by number Dim x as Integer For x = 1 to ActiveWorkbook.Worksheets.Count If Sheets(x).ProtectContents = True Then Sheets(x).Unprotect MsgBox "unProtect Sheet(s)" Else Sheets(x).Protect MsgBox "Protect Sheet(s)" End If Next ================================== Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub ============================= -- steveB Remove "AYN" from email to respond "John F." wrote in message ... Thanks Steve Bell; I gave it a good try, but am getting stuck. The way I'm trying to put this together with your sample, I get an error message at "next ws". Its looking for another "For". Any ideas? "STEVE BELL" wrote: See if you can fit this into your need... ================================= Sub ShtProtecter() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets If sht.ProtectContents = True Then sht.Unprotect MsgBox "unProtect Sheet(s)" Else sht.Protect MsgBox "Protect Sheet(s)" End If Next Application.ScreenUpdating = True End Sub -- steveB Remove "AYN" from email to respond "John F." <John wrote in message ... I set up the following, attached to Icons on personal tool bar ( most is from this forum) ' ProtectionOff Macro ' Keyboard Shortcut: Ctrl+Shift+U Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect ("ABC") Next ws ActiveWorkbook.Unprotect (["DEF"]) End Sub I have another one to PROTECT all sheets and workbook. Questions: 1: Is there an icon I can use to Toggle between Protect / Unprotect? (Instead of a seperate icon for each instruction) 2: Can the above be modified to check each sheets state before trying to change it? (It might be in the correct state already) I'm afraid that trying to, for example, protect a sheet that is protected already, might cause a glitch, under certain circumstances. 3: Is there a way to visually and easily show the state of the active sheet(s) and workbook, (whether its protected or not), Perhaps by colour on the icon(s)? Or by having a message box pop up ( and fade away ) or ??????????? Thanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to password protect multiple workbooks? | Excel Discussion (Misc queries) | |||
Linking to Password Protect Workbooks | Excel Discussion (Misc queries) | |||
password protect copies of workbooks | Excel Discussion (Misc queries) | |||
Running macros with protect workbooks | Excel Discussion (Misc queries) | |||
Password protect a folder with several workbooks | Excel Discussion (Misc queries) |