Protecting Workbooks

It is possible to protect a worksheet but to allow it to be modified using VBA code. To protect the worksheet use VBA Code and set the "UserInterfaceOnly" parameter to True.


ActiveWorkbook.Protect Structure:=True, Windows:=True 
ActiveWorkbook.UnProtect Structure:=True, Windows:=True
ActiveWorkbook.Unprotect
ActiveSheet.Protect

ActiveSheet.Unprotect 
ActiveSheet.Unprotect Password:=sPwdName


Protect all Worksheets

Public Sub ProtectAllSheets 
Dim wshSheet as Worksheet
   For Each wshSheet In ActiveWorkbook.Worksheets

   Next wshSheet
End Sub

Within each edit range you can specify who can edit the range without unlocking the entire worksheet
You can specify that a user provide a range specific password in order to make changes to the worksheet.


Excluding Cells

This can be done using the AllowEditRanges collection
This must be used before calling the Protect Method.
Any exclusions made in this way can be given a title and will display in the Allow Users to Edit Range dialog box.
A range excluded in this way will return True from its Range.AllowEdit property


Excel.AllowEditRanges oEdits = sheet.Protection.AllowEditRanges 
oEdits.Add("-----",sheet.Range["A1"])
sheet.Protect

The other way is to change to locked property for the Range


Worksheet.EnableAutofilter 
Worksheet.EnabeOutlining
Worksheet.EnablePivotTable
Worksheet.EnableSelection
Worksheet.ProtectContents
Worksheet.ProtectDrawingObjects
Worksheet.ProtectionMode
Worksheet.ProtectScenarios


New in 2002 - AllowEditRange and UserAccessList

The AllowEditRange object can be used to specify which users can edit specific ranges and whether they must use a password.
Each worksheet contains an AllowEditRange collection that contains the collection of edit ranges for that worksheet.

ActiveSheet.Protection.AllowEditRanges 

It is possible to provide different combinations of permissions to different ranges.


The list of users for each AllowEditRange object is stored in the UserAccessList collection


Excel 2002 introduced several enhancements to the worksheet protection although these where not incorporated into the new Protection object.
You just have to make do with the EnableSelection property - although the problem is this property is not saved with the workbook.
The new protection object lets us selectively control the features that are accessible to users when we protect a worksheet.
We can decide whether users can sort, alter cell formatting, or insert or delete rows and columns for example.
There is also a new AllowEditRange object that we can use to specify which users can edit specific ranges and whether they must use a password to do so.
We can apply different combinations or permissions to different ranges.


In Excel 2002 it is possible to protect cells so that their content cannot be changed, but the formatting, sorting and other details are under the control of the user.
For management of this new protection option the Protect method of the worksheet has been expanded.
Also the Protection object provides information about the current protection options.
Also you can give individual users (with or without a password) access to selected groups within a protected worksheet.
This is practical when several users are allowed to access the same worksheet but not every one of those users is permitted to make changes.


The AllowEditRange object represents a range of cells on a worksheet that can still be edited after it has been protected.
Each AllowEditRange object can have permissions set for any number of users on a network and can have a separate password.


Be aware of the Locked property of the Range object when using this feature.
When you unlock cells, then protect the worksheet, you are allowing any user access to those cells, regardless of the AllowEditRange objects
When each AllowEditRange object's cells are locked any user can still edit them unless you assign a password or add users to deny them permissions without using a password.


The AllowEditRanges collection represents all AllowEditRange objects that can be edited on a protected worksheet


AllowEditRange Example

The following example loops through a list of named ranges in a worksheet and adds an AllowEditRange item for each one whose name begins with "pc".
It also denies access to the pcNetSales range to all but one user, who can only edit the range with a password.


Sub CreateAllowRanges 
Dim lPos As Long
Dim nm As Name
Dim objAllowEditRange As AllowEditRange
Dim sName As String

With wksAllowEditRange
'loop through the worksheet level named ranges
   For Each nm In .Names
'store the name
      sName = nm.Name
'locate the position of the !
         lPos = InStr(1, sName, "!", vbTextCompare)

         If lPos > 0 Then
'is there a "Pc" just after the exclamation mark point
'if so itís a named range we want to create an AllowEditRange object for
            If Mid(sName, lPos + 1, 2) = "pc" Then
'make sure the cells are locked
'unlocking the cells will allow any user to access them
               nm.RefersToRange.Locked = True

'pull out the worksheet reference (including the "!")
               sName = Right(sName, Len(sName) - lPos)

'create the AllowEditRange object
'remove the old one if it exists
               On Error Resume Next
               Set objAllowEditRange = Nothing
               Set objAllowEditRange = .Protection.AllowEditRanges(sName)
               On Error GoTo 0

               If Not objAllowEditRange Is Nothing Then
                  objAllowEditRange.Delete
               End If

               Set objAllowEditRange = .Protection.AllowEditRanges.Add(sName), nm.RefersToRange)

'if it is the sales named range then
               If sName = "pcNetSales" Then
''add a password, then
'add a user and deny them from editing the range without a password
                  objAllowEditRange.ChangePassword "pcnsw"
                  objAllowEditRange.Users.Add "RCR\UserName", False
               End If
            End If
         End If
      Next nm
   End With

End Sub


objWorkbook.Protect(Password:="mypassword", 
                             Structure:=True,
             Windows:=True)

Password - the case sensitive password for the workbook
Structure - (Optional) Whether to protect the structure of the workbook. The default is false
Windows - (Optional) Whether to protect the workbook windows. The default is false.


Setting structure to true will protect the worksheet order, preventing rearranging of the sheets
Setting windows to true will prevent the windows from being moved or resize. It will keep them in their tiled position.


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext