Section 6.6.Program with Protection


6.6. Program with Protection

Since protecting workbooks, worksheets, and ranges is a multi-step process, it is sometime convenient to automate protectionparticularly if you frequently use the same types of protections or if you want to make sure all protections use the same password.

Excel provides methods for protecting Workbook, Chart, and Worksheet objects as well as subordinate objects for controlling various aspects of protection on Worksheet objects. Figure 6-14 illustrates the relationships between the protection objects.

Figure 6-14. Protection object model


The protection objects are organized in a somewhat unusual way: First, the Workbook and Chart objects don't provide a Protection object, because those objects only allow password-protection. Second, the Worksheet object provides a Protection object that allows you to specify a list of users who can edit ranges on the worksheet. Finally, you set which cells on a worksheet are protected by setting the Range object's Locked property.


Note: The protection objects aren't as consistent as other parts of the Excel object model. You need to know where to look to find the right object.

6.6.1. How to do it

You can use the Worksheet object's Protect and Unprotect methods to work together with the Range object's Locked property to conditionally protect cells on a worksheet. For instance, the following code protects all worksheet cells that contain formulas:

    Set ws = ThisWorkbook.Sheets("Protection")    ' Make sure worksheet is not already protected.    ws.Unprotect    ' Get each used cell in the worksheet.    For Each rng In ws.UsedRange       ' If it contains a formula, lock the cell.       If InStr(rng.Formula, "=") Then          rng.Locked = True       ' Otherwise unlock the cell.       Else          rng.Locked = False       End If    Next    ' Protect the worksheet.    ws.Protect

After you run the preceding code, users can edit data on the worksheet but not cells that contain calculations. The Protect method above doesn't specify a password, so no password is required to unprotect the cells. This isn't very secure, but it would prevent users from making accidental changes to formulas. An alternative is to hardcode a password into the macro or to prompt for a password, as shown in "Program with Passwords and Encryption." For example, the following code gets a password using the Password user form shown earlier:

    frmPassword.Show    ws.Protect frmPassword.Password    Unload frmPassword

Now, the user will be prompted for a password if he attempts to edit a formula.

6.6.2. How it works

Password protection works well when there is one author for a workbook, but it is not very secure for multiple authors since the password must be shared with anyone who wants to make changes. The more people that know a password, the less secure it becomes.

To solve this problem, Excel provides Protection and UserAccessList objects so that you can apply user-based permissions for ranges on a worksheet. User-based permissions solve the multiple-author problem because users are authenticated by the network when they sign on.

Protection with user-based permissions still requires a password to protect the worksheet, but cells are automatically unlocked for certain users so those users aren't required to enter the password. For example, the following code password protects a worksheet but allows members of the Power Users group to edit the range A1:C4:


Note: You have to get a reference to the AllowEditRange object in order to add users that are allowed to edit the range without a password. You can't use Excel's Record Macro feature to see how to add allowed users for a rangeExcel only records the process of adding the named edit range, not adding the users or setting their permissions.
    Dim ws As Worksheet, aer As AllowEditRange    Set ws = ThisWorkbook.Sheets("Protection")    Set aer = ws.Protection.AllowEditRanges.Add("User Range", [A1:C4])    aer.Users.Add "Power Users", True    ws.Protect "Excel2003"

The names of edit ranges on a worksheet must be unique. You can remove previously created edit ranges unprotecting the worksheet and using the Delete method:

    ws.Unprotect    For Each aer In ws.Protection.AllowEditRanges        aer.Delete    Next

Similarly, you can remove users added to an edit range using the Users collection DeleteAll method or the User object's Delete method:

    ws.Unprotect    Set aer = ws.Protection.AllowEditRanges("User Range")    aer.Users("Power Users").Delete

6.6.3. How to get Protection properties

The Protection object provides a set of read-only properties that describe the types of protection in effect on a worksheet. These settings correspond to the settings in the Protect Sheet dialog box and to the arguments used in the Worksheet object's Protect method. For example, the following code displays a report on the Protection property settings in the Immediate window:

    Set ws = ThisWorkbook.Sheets("Protection")    Set prot = ws.Protection    Debug.Print "Can delete:", "Columns?", "Rows?"    Debug.Print , prot.AllowDeletingColumns, prot.AllowDeletingRows    Debug.Print "Can:", "Filter?", "Sort?", "Use Pivot Tables?"    Debug.Print , prot.AllowFiltering, prot.AllowSorting, prot.AllowUsingPivotTables    Debug.Print "Can format:", "Cells?", "Columns?", "Rows?"    Debug.Print , prot.AllowFormattingCells, prot.AllowFormattingColumns, _      prot.AllowFormattingRows    Debug.Print "Can insert:", "Columns?", "Rows?", "Hyperlinks?"    Debug.Print , prot.AllowInsertingColumns, prot.AllowInsertingRows, _      prot.AllowInsertingHyperlinks

You also use the Protection object in order to get a reference to the AllowEditRanges object, which lets you set user-level permissions on a worksheet.

6.6.4. How to add an edit range

Use the AllowEditRanges collection to create ranges that allow edits by specific users. Excel prevents changes to ranges of cells that are protected and locked. The AllowEditRanges settings automatically unlock ranges of cells for the users included in the user access list.

You must remove protection from a worksheet before you can add user-level permissions. For example, the following code unprotects a worksheet, creates a range that allows user-level permissions, and then restores protection:

    Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange, _      usr As UserAccess    Set ws = ThisWorkbook.Sheets("Protection")    ws.Unprotect "Excel2003"    Set aer = ws.Protection.AllowEditRanges.Add("Edit Range", ws.[a1:c4])    Set usr = aer.Users.Add("Power Users", True)    ws.Protect "Excel2003"

6.6.5. How to add users

Use the UserAccessList collection to add users to the user access list of an edit range on a protected worksheet. You can add individual users or groups to the user access list, but the names must be valid user or group names for your system. For example, the following code adds the built-in Users group to the access list for an edit range:

    Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange, _      usr As UserAccess    Set ws = ThisWorkbook.Sheets("Protection")    Set aer = ws.Protection.AllowEditRanges("Edit Range")    Set ual = aer.Users    Set usr = ual.Add("Users", True)

The UserAccessList collection does not support the For Each construct in Visual Basic. Instead, you must use a For statement with a counter get each item in the collection:

    For i = 1 To ual.Count        Set usr = ual(i)        Debug.Print usr.Name    Next

6.6.6. What about...

Workbook, Worksheet, and Chart objects all provide protection members. Each of those objects provides 100 or more other members as well. To help navigate among the various ways to protect items in code, Tables 6-2 through 6-7 list the protection members and subordinate objects for each of those objects. You can get Help on any of these members from the Visual Basic editor.

Table 6-2. Workbook protection members. Use the Workbook object to protect the order of worksheets, window and pane layout, and to set passwords for shared workbooks
Protect ProtectSharing ProtectStructure
ProtectWindows Unprotect UnprotectSharing


Table 6-3. Worksheet protection members. Use the Worksheet object to protect locked cells and other items on the worksheet
Protect ProtectContents ProtectDrawingObjects
Protection ProtectionMode ProtectScenarios
Unprotect   


Table 6-4. Chart protection members. Use the Chart object to protect chart appearance and data
Protect ProtectContents ProtectData
ProtectDrawingObjects ProtectFormatting ProtectGoalSeek
ProtectionMode ProtectSelection Unprotect


Table 6-5. Protection object members. Use the Worksheet object's Protection method to get a reference to the Protection object
AllowDeletingColumns AllowDeletingRows AllowEditRanges
AllowFiltering AllowFormattingCells AllowFormattingColumns
AllowFormattingRows AllowInsertingColumns AllowInsertingHyperlinks
AllowInsertingRows AllowSorting AllowUsingPivotTables


Table 6-6. AllowEditRanges/AllowEditRange members. Use the Protection object's AllowEditRanges property to get a reference to the AllowEditRanges collection
Add ChangePassword Count
Delete Range Title
Unprotect Users  


Table 6-7. UserAccessList members. Use the AllowEditRange object's Users property to get a reference to the UserAccessList collection
Add AllowEdit Count
Delete DeleteAll Name




    Excel 2003 Programming. A Developer's Notebook
    Excel 2003 Programming: A Developers Notebook (Developers Notebook)
    ISBN: 0596007671
    EAN: 2147483647
    Year: 2004
    Pages: 133
    Authors: Jeff Webb

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net