Solved

Macro for turning On and Off Protection

Posted on 2016-08-11
11
43 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 45

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 17

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
 
LVL 45

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 17

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

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 45

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 45

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 17

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now