• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

Macro for turning On and Off Protection

EE Pros,

How do I construct a Macro that turns on and off Protection on the active sheet?

B.
Lock-and-Unlock-Macro.xlsm
0
Bright01
Asked:
Bright01
  • 4
  • 4
  • 3
1 Solution
 
Martin LissRetired ProgrammerCommented:
You probably don't need it if you remove other protection and  do protection this way.

Sheets("Sheet3").Protect UserInterfaceOnly:=True
0
 
Roy CoxGroup Finance ManagerCommented:
Martin's suggestion allows macros to run without turning off sheet protection as explained here There's also an example workbook.

If you want to allow manual entry into the sheets then try this code assigned to your shapes.

Option Explicit

Sub ToggleProtectShts()
Dim ws As Worksheet

 ''///test if protected, skips protected sheets
 With ActiveSheet
   If Not .ProtectContents = True Then
     ''///protect the sheet with password "secret"
     .Protect
     .EnableSelection = xlUnlockedCells
     MsgBox .Name & " is now protected", vbOKOnly, "Finished"
     ''///protect the sheet with password "secret"
     Else: .Unprotect
     MsgBox .Name & " is now unprotected", vbOKOnly, "Finished"
    End If
   End With
End Sub

Open in new window

Lock-and-Unlock-Macro.xlsm
0
 
Bright01Author Commented:
Martin and Roy,

I have had to add protection to individual sheets due to some of the macros that run in my WB.  I'm already using .Protect UserInterfaceOnly:=True for all worksheets.  

I need to be more specific.  I'm looking for a way for a User to Lock/Unlock a specific Worksheet by firing the macro on that particular sheet.  This way I can add the button, link the Macro and have the User have the ability to unlock a specific sheet when they need to make some structural changes to that sheet, without unlocking the model or exposing the password.  So, the button should say "Locked" or "Unlocked".  When it reads "Unlocked" and you fire the macro, it unlocks the WS through the code and thus not having to give out the password.  The text on the box changes to "Lock".  Press "Lock" and it relocks the sheet with the password embedded in the macro.

Does that make sense?

Thank you for both jumping in!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Martin LissRetired ProgrammerCommented:
That doesn't make sense to me. If you have a button that let's the user unlock the sheet any time he wants to then you might as well leave the sheet unlocked.

If you go to this site it explains that you can still protect the sheet but allow some structural changes like 'AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns and AllowInsertingRows, with a statement like this.

Sheets("Sheet1").Protect UserInterfaceOnly:=True, AllowInsertingColumns:=True

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
I was going to suggest using Forms buttons and changing the Caption within the code, but I was leaving for work. I'll amend the example
0
 
Roy CoxGroup Finance ManagerCommented:
This code will change the caption. I have used a Forms Button and a TextBox. It is important that the control's name ends in Lock. To do this select the button or shape then change the name displayed in the Name Box and click enter

NameBox.png
Option Explicit

Sub ToggleProtectShts()
    Dim ws As Worksheet
    Dim sCap As String, sNm As String
    Dim shp As Shape

    ''///test if protected, skips protected sheets
    With ActiveSheet
        If Not .ProtectContents = True Then
            ''///protect the sheet with password "secret"
            .Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                     True
            .EnableSelection = xlUnlockedCells
            sCap = "Unlock Me"
            MsgBox .Name & " is now protected", vbOKOnly, "Finished"
            ''///protect the sheet with password "secret"
        Else: .Unprotect
            sCap = "Lock Me"
            MsgBox .Name & " is now unprotected", vbOKOnly, "Finished"
        End If

        For Each shp In .Shapes
            If Right(shp.Name, 4) = "Lock" Then
                sNm = shp.Name
                .Shapes(sNm).TextFrame.Characters.Text = sCap
            End If
        Next shp
    End With
End Sub

Open in new window

Lock-and-Unlock-Macro.xlsm
0
 
Bright01Author Commented:
Thanks Roy.  I can use this.

Martin, I needed this so that a User could lock and relock a sheet without knowing the password.

Thank you both,

B.
0
 
Martin LissRetired ProgrammerCommented:
I understand that you selected Roy's solution and that's OK, you can't win them all, but I don't understand why if you give the users the ability to turn the protection off via a button, you need the password at all?
0
 
Bright01Author Commented:
Martin,

You win nearly 99% of the time ;-)

The reason is that the User should not be exposed to the Password.  So by turning it on and off via a macro/button, the User still does not know the password.  And it is only for certain WSs.

I hope that makes sense.

B.
0
 
Martin LissRetired ProgrammerCommented:
Are you concerned about exposing the workbook password? If so area you aware that you can have different passwords for each sheet and that not all sheets need to be passworded?
0
 
Roy CoxGroup Finance ManagerCommented:
To use the code without it being automated then the password can be excluded from the code, the password prompt will then still appear.

The sheet should really be re-protected when the sheet is changed and all sheets should have their passwords restored when the workbook is closed.

Glad you could make use of the code.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now