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,


ws.Protect

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.

Warning
If you forget the password, you cannot unprotect the worksheet or workbook.

Protect options

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 (ws.ProtectDrawingObjects)

DrawingObjects

True to protect shapes. The default value is True.

Protects the locked cells.

Scenarios

True to protect scenarios. This argument is valid only for worksheets. The default value is True.

AllowFormattingCells

True allows the user to format any cell on a protected worksheet. The default value is False.

AllowFormattingColumns

True allows the user to format any column on a protected worksheet. The default value is False.

AllowFormattingRows

True allows the user to format any row on a protected. The default value is False.

AllowInsertingColumns

True allows the user to insert columns on the protected worksheet. The default value is False.

AllowInsertingRows

True allows the user to insert rows on the protected worksheet. The default value is False.

AllowInsertingHyperlinks

True allows the user to insert hyperlinks on the worksheet. The default value is False.

AllowDeletingColumns

True allows the user to delete columns on the protected worksheet

AllowDeletingRows

True allows the user to delete rows on the protected worksheet

AllowSorting

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.

AllowFiltering

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.

AllowUsingPivotTables

True allows the user to use pivot table reports on the protected worksheet. The default value is False.