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.
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext