Protect a worksheet using VBA
When you protect a worksheet, you are preventing the user from changing values in cells and making other changes to the worksheet. Protecting a worksheet can be as simple as calling
Dim ws As Worksheet: Set ws = Dim strPassword As String: strPassword = ws.Protect Password:=strPassword
or, the Password being optional,
By default, all cells are locked, but this does not take effect until you protect the worksheet. When you protect the sheet, all cells are locked except those that you have specifically unlocked.
To unprotect, depending on whether Password was supplied during protection.
Dim strPassword As String: strPassword = ws.Unprotect Password:=strPassword
If the password is omitted, you can unprotect the worksheet or workbook without specifying a password.
|If you forget the password, you cannot unprotect the worksheet or workbook.|
When inserting the code using the Code VBA add-in all arguments are set explicitly. This makes it clear which features are protected (by default) and which not. This makes it easier to decide in which case you want to deviate from the defaults.
Dim ws As Worksheet: Set ws = Dim strPassword As String: strPassword = ws.Protect Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _ UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _ AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _ AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _ AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False
Give your macros access to locked cells - the UserInterfaceOnly Option
When you lock cells on a worksheet, that protection applies to VBA operations as well as user actions. If your VBA code attempts to modify a locked cell, you will get a runtime error 1004. One way to work around this is to unprotect the sheet before the relevant VBA runs and then reprotect it after the VBA is finished. This is sort of a messy solution. The better way is to use the UserInterfaceOnly flag when you protect the sheet via VBA. You can specify UserInterfaceOnly only in VBA code. There is no user interface element (command bar, menu, etc) for this option; you must use VBA. When you protect a sheet with UserInterfaceOnly, protection is applied to user actions (basically, keyboard and mouse operations), but VBA is free to modify the worksheet as if there was no protection. The UserInterfaceOnly setting is not saved when you close the workbook, so you need to set it when the workbook is opened. The best place to do this is in the Workbook_Open event procedure in module ThisWorkbook.
Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect UserInterfaceOnly:=True Next ws End Sub
You don't need to protect the sheet with a password just to use the user interface option. If the sheet already is protected with a password just use the userinterfaceonly line.
Protection object and worksheet Protection property
You can also view a protection settings via the Protection object associated with the Worksheet and
if it does not have the required value change it by calling the
Protect method the appropriate argument set, e.g.
If ActiveSheet.Protection.AllowInsertingColumns = False Then ActiveSheet.Protect AllowInsertingColumns:=True End If
Worksheet properties to check if feature is protected
You can check if contents are protected using:
Dim booProtectContents As Boolean booProtectContents = ws.ProtectContents
Similarly, you can check protection of Scenarios (
ws.ProtectScenarios and DrawingObjects (
True to protect shapes. The default value is True.
Protects the locked cells.
True to protect scenarios. This argument is valid only for worksheets. The default value is True.
True allows the user to format any cell on a protected worksheet. The default value is False.
True allows the user to format any column on a protected worksheet. The default value is False.
True allows the user to format any row on a protected. The default value is False.
True allows the user to insert columns on the protected worksheet. The default value is False.
True allows the user to insert rows on the protected worksheet. The default value is False.
True allows the user to insert hyperlinks on the worksheet. The default value is False.
True allows the user to delete columns on the protected worksheet
True allows the user to delete rows on the protected worksheet
True allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected. The default value is False.
True allows the user to set filters on the protected worksheet. Users can change filter criteria but can not enable or disable an auto filter. Users can set filters on an existing auto filter. The default value is False.
True allows the user to use pivot table reports on the protected worksheet. The default value is False.