Solved

Macro for turning On and Off Protection

Posted on 2016-08-11
11
55 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
  • 4
  • 4
  • 3
11 Comments
 
LVL 46

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 18

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 46

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 18

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 18

Accepted Solution

by:
Roy_Cox earned 500 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 46

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 46

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 18

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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