Excel Unprotect Sheet (Table of Contents)
- Unprotect Sheet in Excel
- How to Protect and Unprotect Sheet in Excel?
- How to Protect and Unprotect Multiple Sheets using Excel VBA Code?
Unprotect Sheet in Excel
We usually keep sharing a file to our subordinates. Often we do not want them to make any kind of changes to the sheet so that it remains unaltered.
Before I explain you the process of protecting & unprotecting excel sheets let me tell you an important thing about the protection of sheet in excel. This is not complete security to your sheet, people still can see the data, insights, and everything, the only thing is that they cannot make any changes to the sheet that’s all.
In this article, I will show you how to protect sheet and unprotect them with a password in excel. Follow this article closely to learn the process of locking & unlocking.
How to Protect and Unprotect Sheet in Excel?
Below are the steps to protect and unprotect sheet in excel.
#1- Protect sheet & Prevent Users from Making Changes
Assume you are sending the below file to all the department managers and you don’t want them to make any changes rather just see the numbers.
Follow below steps to protect your sheet.
Step 1: Go to the Review tab and click on the Protect Sheet.
Step 2: Now you will see Protect Sheet window which asks you to put the password.
Step 3: Enter your password carefully here. Because you need the same password to unprotect the sheet later if you want to make some changes. A password is case sensitive so remember the exact characters as well. Here we have set the password as 12345.
Step 4: At the same time we can allow a user to make some kind of changes like Insert Hyperlink, Insert row or column etc… If you are allowing them to make changes then you need to select those option under “Allow all users of this worksheet to”.Click on OK.
Step 5: It will ask you to re-enter the password one more time. Re-enter and click on OK to complete the process.
Ok, now we have protected this sheet. Users cannot make any changes.
In this sheet, users cannot do anything else apart from seeing the data. The only thing they can do is they can insert a new sheet and only work on that newly inserted sheet only
#2 – How to Unprotect Sheet in Excel?
Now we have protected our sheet which restricts the users from modifying the document. If you want to make changes you need to unprotect the sheet first and make changes in excel. Follow below steps to unprotect excel sheet.
Step 1: Go to Review tab & click on Unprotect Sheet.
Step 2: As soon as you click on Unprotect it will ask you to enter the same password which you have typed while protecting the sheet.
Step 3: After the password is entered click on OK.
Step 4: If the password is correct you can make changes or else it will say Password is Incorrect.
How to Protect and Unprotect Multiple Sheets using Excel VBA Code?
Below are the steps to protect and unprotect multiple sheets using VBA Code in excel.
#1 – Protect Multiple sheets Using Excel VBA Code
What if you have many sheets, can you keep protecting the sheets one by one? It is a herculean task to do. But using VBA code we can protect all the sheets in few seconds, all you need to do is copy below code and paste in VBA editor.
Sub Protect_All_Worksheets()
Dim Ws As Worksheet
For Each WsIn ActiveWorkbook.Worksheets
Ws.Protect Password:="12345"
Ws.EnableSelection = xlNoSelection
Next Ws
End Sub
Step 1: Click on Visual Basic under Developer tab.
Step 2: Go to Insert & click on Module.
Step 3: Now we can see that new module has been added as Module 1 on the left-hand side of the panel.
Step 4: Double click on the module and paste the code.
Step 5: After pasting this code in Module 1 click on RUN button on top. Or else you can press F5 key by placing a cursor inside the macro.
As soon as the code is run it will protect all the sheets in your workbook.
#2 – Unprotect Multiple sheets Using Excel VBA Code
We know how to protect multiple sheets using VBA code in simple code in few seconds. If you have locked multiple sheets it is not an easy task to unprotect one by one. For this also I have written VBA code, copy & paste the code.
Sub Unprotect_All_Worksheets()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Unprotect Password:="12345"
Next Ws
End Sub
Things to Remember
- You need to save the file as Macro-Enabled Workbook after pasting the code to the module.
- You can change the password to your own password. Remove 12345 and enter your password inside the double quotes.
- A password is case sensitive.
- If you forget the password you need to google research and upload your file to the website to remove password from the file.
Recommended Articles
This has been a guide to Unprotect Sheet in Excel. Here we also discuss how to protect and manually and also by using VBA code along with practical example and downloadable excel template. You can also go through our other suggested articles –
