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, over types essential formulas and cell values. It’s even worse when that person happens to be ourselves; 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 the accidental alteration by an unknowing user.
Sheet protection is particularly frustrating because 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.
Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples. This is the best way to learn. You’ll be able to see the solutions in action, plus the file will be useful for future reference. The support files are available for FREE to newsletter subscribers.
Click below to subscribe and gain access to the subscriber area. You will also receive:
- My favorite tips and tricks direct to your inbox
- Exclusive content (which is only available to subscribers)
- FREE tools and downloads
If you’re already a subscriber, click here to log-in to the subscriber downloads area.
The filename for this post is 0016 VBA Protect and unprotect sheets.zip
Contents
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").Protect
But there are lots of ways to reference sheets for protecting or unprotecting.
Using the active sheet
The active sheet is whichever sheet is currently being used within the Excel window.
ActiveSheet.Protect
Applying 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.Protect
Later 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.
Protect a sheet without a password
Sub ProtectSheet() 'Protect a worksheet Sheets("Sheet1").Protect End Sub
Unprotect a sheet (no password)
Sub UnProtectSheet() 'Unprotect a worksheet Sheets("Sheet1").Unprotect End Sub
Protecting 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.
Protect sheet with password
Sub ProtectSheetWithPassword() 'Protect worksheet with a password Sheets("Sheet1").Protect Password:="myPassword" End Sub
Unprotect sheet with a password
Sub UnProtectSheetWithPassword() 'Unprotect a worksheet with a password Sheets("Sheet1").Unprotect Password:="myPassword" End Sub
Catching 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 Sub
If you forget a password, don’t worry, the protection is easy to remove.
Generate accurate VBA code in seconds with AutoMacro
AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.
Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.
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.
Protect contents
Sub ProtectSheetContents() 'Apply worksheet contents protection only Sheets("Sheet1").Protect Password:="myPassword", _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=False End Sub
Protect objects
Sub ProtectSheetObjects() 'Apply worksheet objects protection only Sheets("Sheet1").Protect Password:="myPassword", _ DrawingObjects:=True, _ Contents:=False, _ Scenarios:=False End Sub
Protect scenarios
Sub ProtectSheetScenarios() 'Apply worksheet scenario protection only Sheets("Sheet1").Protect Password:="myPassword", _ DrawingObjects:=False, _ Contents:=False, _ Scenarios:=True End Sub
Protect 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 Sub
Applying 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 Sub
Protect 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 Sub
Unprotect 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 Sub
Checking 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 Sub
Check if Sheet objects are protected
Sub CheckIfSheetObjectsProtected() 'Check if worksheet objects are protected If Sheets("Sheet1").ProtectDrawingObjects Then MsgBox "Protected Objects" End Sub
Check if Sheet scenarios are protected
Sub CheckIfSheetScenariosProtected() 'Check if worksheet scenarios are protected If Sheets("Sheet1").ProtectScenarios Then MsgBox "Protected Scenarios" End Sub
Changing 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 unlock, 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 Sub
Lock all cells
Sub LockAllCells() 'Changing the options to lock or unlock cells all cells Sheets("Sheet1").Cells.Locked = True End Sub
Lock a chart
Sub LockAChart() 'Changing the options to lock or unlock charts Sheets("Sheet1").ChartObjects("Chart 1").Locked = True End Sub
Lock a shape
Sub LockAShape() 'Changing the option to lock or unlock shapes Sheets("Sheet1").Shapes("Rectangle 1").Locked = True End Sub
ActiveWorkbook.Worksheets
Sub LockAScenario() 'Changing the option to lock or unlock a scenario Sheets("Sheet1").Scenarios("scenarioName").Locked = True End Sub
Allowing 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 Sub
Allow selection of any cells
Sub AllowSelectionAnyCells() 'Allowing selection of locked or unlocked cells Sheets("Sheet1").EnableSelection = xlNoRestrictions End Sub
Allow selection of unlocked cells
Sub AllowSelectionUnlockedCells() 'Allowing selection of unlocked cells only Sheets("Sheet1").EnableSelection = xlUnlockedCells End Sub
Don’t allow selection of any cells
Sub NoSelectionAllowed() 'Do not allow selection of any cells Sheets("Sheet1").EnableSelection = xlNoSelection End Sub
Allowing 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 Sub
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 Sub
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.
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Hello I am trying to insert similar code shared by you, but i am getting the error. Can you help me out with the code below –
Sub RefreshAllPivotTables()
ActiveSheet.Unprotect Password:=“Password”
ThisWorkbook.RefreshAll
ActiveSheet.Protect Password:=“Password”
Sub AllowSheetActionsWhenProtected()
‘Allowing certain actions even if the worksheet is protected
Sheets(“Sheet1″).Protect Password:=”Password”, _
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 Sub
P.S : I am new to VBA so m not able to compile the code properly, please help me out on this one
I think you were pretty much there. You just needed to combine the two codes together:
Hi,
I have used to macro first time, the macro is applied and working fine; however, when i protect the sheet, the macro does not work.
Can you please help me in this to fix the issue.
Sub GoalSeek()
‘
‘ GoalSeek Macro
‘
‘
Range(“Q9”).GoalSeek Goal:=Range(“U6”), ChangingCell:=Range(“U4”)
End Sub
Hi Abu,
There is a setting which needs to be changed, so that macros are permitted to make changes to a locked worksheet. The settings is UserInterfaceOnly, it needs to be set to TRUE.
Look at the section above called: Allowing VBA code to make changes, even when protected.
Once that setting has been applied to the sheet, you don’t need to apply it again.
Hi,
thank you for your support, i tried to use the code it works fine only if sheet is active; when i close the sheet and re-open again, it does not work and through the same error.
Sub GoalSeek()
‘ GoalSeek Macro
Range(“Q9”).GoalSeek Goal:=Range(“U6”), ChangingCell:=Range(“U4”)
End Sub
Sub ProtectSheetAll()
‘Apply worksheet protection to contents, objects and scenarios
Sheets(“RepaymentSchedule”).Protect Password:=”myPassword”, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
End Sub
When using Range by itself, Excel assumes you want the ActiveSheet. To be specific about the sheet, you’ll need to define the sheet:
Check out this post from Jon Acampora to understand more:
https://www.excelcampus.com/vba/vba-context-assumptions/
Hi, this is very helpful! In my VBA code, I allow adding rows & columns, but the user cannot edit those new rows/columns because the cells are locked. Is there any way to allow the user to add rows/columns and have them unlocked so the user can only edit the rows/columns added?
Hi Anne – Just change the locked property of the added range from true to false.
Your code examples and explanations are great. I only have one question (at this time) …. does the code for Allowing VBA Code Changes on protected sheet go on the sheet code or on the ‘ThisWorkbook’ Code?
Hi Terri – the code example reference the specific sheet by using Sheets(“Sheet1”), so it shouldn’t matter.
I would recommend using it in a standard code module. I would only recommend using it in ThisWorkbook or a Sheet module if you’re using Events to trigger the macro.