?
Solved

Macro for turning On and Off Protection

Posted on 2016-08-11
11
Medium Priority
?
75 Views
Last Modified: 2016-08-13
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
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41753179
You probably don't need it if you remove other protection and  do protection this way.

Sheets("Sheet3").Protect UserInterfaceOnly:=True
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41753395
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
 

Author Comment

by:Bright01
ID: 41753596
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.

 
LVL 49

Expert Comment

by:Martin Liss
ID: 41753793
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
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41754034
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
 
LVL 22

Accepted Solution

by:
Roy Cox earned 2000 total points
ID: 41754081
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
 

Author Closing Comment

by:Bright01
ID: 41754943
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41754959
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
 

Author Comment

by:Bright01
ID: 41755013
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41755054
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
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41755216
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question