Protecting and unprotecting sheets is a common action for an Excel user. There is nothing worse than when somebody, who doesn’t know what they’re doing, overtypes essential formulas and cell values. It’s even worse when that person happens to be us; all it takes is one accidental keypress, and suddenly the entire worksheet is filled with errors. In this post, we explore using VBA to protect and unprotect sheets.
Protection is not foolproof but prevents accidental alteration by an unknowing user.
Sheet protection is particularly frustrating as it has to be applied one sheet at a time. If we only need to protect a single sheet, that’s fine. But if we have more than 5 sheets, it is going to take a while. This is why so many people turn to a VBA solution.
The VBA Code Snippets below show how to do most activities related to protecting and unprotecting sheets.
Table of Contents
- Adapting the code for your purposes
- Protect and unprotect: basic examples
- Protecting and unprotecting with a password
- VBA Protect sheet with password
- VBA Unprotect sheet with a password
- Using a password based on user input
- Catching errors when incorrect password entered
- Applying protection to different parts of the worksheet
- Applying protection to multiple sheets
- Protect all worksheets in the active workbook
- Protect the selected sheets in the active workbook
- Unprotect all sheets in active workbook
- Checking if a worksheet is protected
- Check if Sheet contents is protected
- Check if Sheet objects are protected
- Check if Sheet scenarios are protected
- Changing the locked or unlocked status of cells, objects and scenarios
- Allowing actions to be performed even when protected
- Allow sheet actions when protected
- Allow selection of any cells
- Allow selection of unlocked cells
- Don’t allow selection of any cells
- Allowing VBA code to make changes, even when protected
- Allowing the use of the Group and Ungroup feature
- Conclusion
Download the example file: Join the free Insiders Program and gain access to the example file used for this post.
File name: 0016 VBA Protect and Unprotect Sheets.zip
Adapting the code for your purposes
Unless stated otherwise, every example below is based on one specific worksheet. Each code includes Sheets(“Sheet1”)., this means the action will be applied to that specific sheet. For example, the following protects Sheet1.
Sheets("Sheet1").ProtectBut there are lots of ways to reference sheets for protecting or unprotecting. Therefore we can change the syntax to use one of the methods shown below.
Using the active sheet
The active sheet is whichever sheet is currently being used within the Excel window.
ActiveSheet.ProtectApplying a sheet to a variable
If we want to apply protection to a sheet stored as a variable, we could use the following.
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.ProtectLater in the post, we look at code examples to loop through each sheet and apply protection quickly.
Protect and unprotect: basic examples
Let’s begin with some simple examples to protect and unprotect sheets in Excel.
Protect a sheet without a password
Sub ProtectSheet()
'Protect a worksheet
Sheets("Sheet1").Protect
End SubUnprotect a sheet (no password)
Sub UnProtectSheet()
'Unprotect a worksheet
Sheets("Sheet1").Unprotect
End SubProtecting and unprotecting with a password
Adding a password to give an extra layer of protection is easy enough with VBA. The password in these examples is hardcoded into the macro; this may not be the best for your scenario. It may be better to apply using a string variable, or capturing user passwords with an InputBox.
VBA Protect sheet with password
Sub ProtectSheetWithPassword()
'Protect worksheet with a password
Sheets("Sheet1").Protect Password:="myPassword"
End SubVBA Unprotect sheet with a password
Sub UnProtectSheetWithPassword()
'Unprotect a worksheet with a password
Sheets("Sheet1").Unprotect Password:="myPassword"
End SubNOTE – It is not necessary to unprotect, then re-protect a sheet to change the settings. Instead, just protect again with the new settings.
Using a password based on user input
Using a password that is included in the code may partly defeat the benefit of having a password. Therefore, the codes in this section provide examples of using VBA to protect and unprotect based on user input. In both scenarios, clicking Cancel is equivalent to entering no password.
Protect with a user-input password
Sub ProtectSheetWithPasswordFromUser()
'Protect worksheet with a password
Sheets("Sheet1").Protect Password:=InputBox("Enter a protection password:")
End SubUnprotect with a user-input password
Sub UnProtectSheetWithPasswordFromUser()
'Protect worksheet with a password
Sheets("Sheet1").Unprotect _
Password:=InputBox("Enter a protection password:")
End SubCatching errors when incorrect password entered
If an incorrect password is provided, the following error message displays.
The code below catches the error and provides a custom message.
Sub CatchErrorForWrongPassword()
'Keep going even if error found
On Error Resume Next
'Apply the wrong password
Sheets("Sheet1").Unprotect Password:="incorrectPassword"
'Check if an error has occured
If Err.Number <> 0 Then
MsgBox "The Password Provided is incorrect"
Exit Sub
End If
'Reset to show normal error messages
On Error GoTo 0
End SubIf you forget a password, don’t worry, the protection is easy to remove.
Applying protection to different parts of the worksheet
VBA provides the ability to protect 3 aspects of the worksheet:
- Contents – what you see on the grid
- Objects – the shapes and charts which are on the face of the grid
- Scenarios – the scenarios contained in the What If Analysis section of the Ribbon
By default, the standard protect feature will apply all three types of protection at the same time. However, we can be specific about which elements of the worksheet are protected.
Protect contents
Sub ProtectSheetContents()
'Apply worksheet contents protection only
Sheets("Sheet1").Protect Password:="myPassword", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
End SubProtect objects
Sub ProtectSheetObjects()
'Apply worksheet objects protection only
Sheets("Sheet1").Protect Password:="myPassword", _
DrawingObjects:=True, _
Contents:=False, _
Scenarios:=False
End SubProtect scenarios
Sub ProtectSheetScenarios()
'Apply worksheet scenario protection only
Sheets("Sheet1").Protect Password:="myPassword", _
DrawingObjects:=False, _
Contents:=False, _
Scenarios:=True
End SubProtect contents, objects and scenarios
Sub ProtectSheetAll()
'Apply worksheet protection to contents, objects and scenarios
Sheets("Sheet1").Protect Password:="myPassword", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End SubApplying protection to multiple sheets
As we have seen, protection is applied one sheet at a time. Therefore, looping is an excellent way to apply settings to a lot of sheets quickly. The examples in this section don’t just apply to Sheet1, as the previous examples have, but include all worksheets or all selected worksheets.
Protect all worksheets in the active workbook
Sub ProtectAllWorksheets()
'Create a variable to hold worksheets
Dim ws As Worksheet
'Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
'Protect each worksheet
ws.Protect Password:="myPassword"
Next ws
End SubProtect the selected sheets in the active workbook
Sub ProtectSelectedWorksheets()
Dim ws As Worksheet
Dim sheetArray As Variant
'Capture the selected sheets
Set sheetArray = ActiveWindow.SelectedSheets
'Loop through each worksheet in the active workbook
For Each ws In sheetArray
On Error Resume Next
'Select the worksheet
ws.Select
'Protect each worksheet
ws.Protect Password:="myPassword"
On Error GoTo 0
Next ws
sheetArray.Select
End SubUnprotect all sheets in active workbook
Sub UnprotectAllWorksheets()
'Create a variable to hold worksheets
Dim ws As Worksheet
'Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
'Unprotect each worksheet
ws.Unprotect Password:="myPassword"
Next ws
End SubChecking if a worksheet is protected
The codes in this section check if each type of protection has been applied.
Check if Sheet contents is protected
Sub CheckIfSheetContentsProtected()
'Check if worksheets contents is protected
If Sheets("Sheet1").ProtectContents Then MsgBox "Protected Contents"
End SubCheck if Sheet objects are protected
Sub CheckIfSheetObjectsProtected()
'Check if worksheet objects are protected
If Sheets("Sheet1").ProtectDrawingObjects Then MsgBox "Protected Objects"
End SubCheck if Sheet scenarios are protected
Sub CheckIfSheetScenariosProtected()
'Check if worksheet scenarios are protected
If Sheets("Sheet1").ProtectScenarios Then MsgBox "Protected Scenarios"
End SubChanging the locked or unlocked status of cells, objects and scenarios
When a sheet is protected, unlocked items can still be edited. The following codes demonstrate how to lock and unlock ranges, cells, charts, shapes and scenarios.
When the sheet is unprotected, the lock setting has no impact. Each object becomes locked on protection.
All the examples in this section set each object/item to lock when protected. To set as unlocked, change the value to False.
Lock a cell
Sub LockACell()
'Changing the options to lock or unlock cells
Sheets("Sheet1").Range("A1").Locked = True
End SubLock all cells
Sub LockAllCells()
'Changing the options to lock or unlock cells all cells
Sheets("Sheet1").Cells.Locked = True
End SubLock a chart
Sub LockAChart()
'Changing the options to lock or unlock charts
Sheets("Sheet1").ChartObjects("Chart 1").Locked = True
End SubLock a shape
Sub LockAShape()
'Changing the option to lock or unlock shapes
Sheets("Sheet1").Shapes("Rectangle 1").Locked = True
End SubLock a Scenario
Sub LockAScenario()
'Changing the option to lock or unlock a scenario
Sheets("Sheet1").Scenarios("scenarioName").Locked = True
End SubAllowing actions to be performed even when protected
Even when protected, we can allow specific operations, such as inserting rows, formatting cells, sorting, etc. These are the same options as found when manually protecting the sheet.
Allow sheet actions when protected
Sub AllowSheetActionsWhenProtected()
'Allowing certain actions even if the worksheet is protected
Sheets("Sheet1").Protect Password:="myPassword", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=False, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=False, _
AllowFiltering:=False, _
AllowUsingPivotTables:=False
End SubAllow selection of any cells
Sub AllowSelectionAnyCells()
'Allowing selection of locked or unlocked cells
Sheets("Sheet1").EnableSelection = xlNoRestrictions
End SubAllow selection of unlocked cells
Sub AllowSelectionUnlockedCells()
'Allowing selection of unlocked cells only
Sheets("Sheet1").EnableSelection = xlUnlockedCells
End SubDon’t allow selection of any cells
Sub NoSelectionAllowed()
'Do not allow selection of any cells
Sheets("Sheet1").EnableSelection = xlNoSelection
End SubAllowing VBA code to make changes, even when protected
Even when protected, we still want our macros to make changes to the sheet. The following VBA code changes the setting to allow macros to make changes to a protected sheet.
Sub AllowVBAChangesOnProtectedSheet()
'Enable changes to worksheet by VBA code, even if protected
Sheets("Sheet1").Protect Password:="myPassword", _
UserInterfaceOnly:=True
End SubUnfortunately, this setting is not saved within the workbook. It needs to be run every time the workbook opens. Therefore, calling the code in the Workbook_Open event of the Workbook module is probably the best option.
Allowing the use of the Group and Ungroup feature
To enable users to make use of the Group and Ungroup feature of protected sheets, we need to allow changes to the user interface and enable outlining.
Sub AllowGroupingAndUngroupOnProtectedSheet()
'Allow user to group and ungroup whilst protected
Sheets("Sheet1").Protect Password:="myPassword", _
UserInterfaceOnly:=True
Sheets("Sheets1").EnableOutlining = True
End SubAs noted above the UserInterfaceOnly setting is not stored in the workbook; therefore, it needs to be run every time the workbook opens.
Conclusion
Wow! That was a lot of code examples; hopefully, this covers everything you would ever need for using VBA to protect and unprotect sheets.
Related posts:
- Office Scripts – Workbook & worksheet protection
- VBA Code to Password Protect an Excel file
- VBA code to Protect and Unprotect Workbooks
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.
It isn’t safe to store password in VBA code!!
You can get this password in 10 seconds, even if you protect the VBA code itself with another password…
I agree 100%. Nothing in Excel is safe. But protection prevents the average user from messing things up. Which has got to be better than no protection at all.