Solved

Macro for turning On and Off Protection

Posted on 2016-08-11
11
48 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 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 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 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 46
macro for each dropdown 15 44
Excel for Mac - How make those Tabs larger? 2 31
Update As Well As Add 6 37
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 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

18 Experts available now in Live!

Get 1:1 Help Now