Toggle sheet protection

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Toggle sheet protection

Post by ABabeNChrist »

I have a a few worksheets that will every now and then, need the removal of sheet protection, this will enable the user to make changes if needed.
At the present moment I use this code to add protection

Code: Select all

 ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:="" 
and this to remove protection

Code: Select all

ActiveSheet.Unprotect Password:=""
I would like to be able to toggle back and forth.
Here is what I was able to come up with so far, I believe "ActiveSheet" should be replaced with "ActiveWorkbook"

Code: Select all

With ActiveWorkbook.Protect("")
If ActiveWorkbook.Unprotect("") Then
Else
ActiveWorkbook.Protect ("")
I'm close but, but not close enought. lol

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Toggle sheet protection

Post by HansV »

Your With ... End With block makes no sense because (a) Protect is not a property, and (b) you don't do anything with it within the block.

Try this:

Code: Select all

Sub ToggleProtection()
  If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect
  Else
    ActiveSheet.Protect DrawingObjects:=False
  End If
End Sub
You don't have to specify Password:="", and neither do you have to specify Contents:=True and Scenarios:=True, because those are the default settings.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Toggle sheet protection

Post by ABabeNChrist »

Thank you HansV
I dont know what I was thinking when I put the "With" in front of the code. I know its "If"