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.
|