Excel Unprotect Sheet (Table of Contents)
- Introduction to Unprotect Sheet in Excel
- How to Protect and Unprotect Sheet in Excel?
- How to Protect and Unprotect Multiple Sheets using Excel VBA Code?
Introduction to Unprotect Sheet in Excel
We usually keep sharing a file with our subordinates. Often we do not want them to make any kind of changes to the sheet so that it remains unaltered. Before we explain to you the process of protecting & unprotecting excel sheets, let me tell you an important thing about the protection of sheets 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. This article will show you how to protect sheets 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 the 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 the 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 allow them to make changes, you need to select those options 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 the below steps to unprotect the excel sheet.
Step 1: Go to the 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.
23 Online Courses | 9 Hands-on Projects| 110+ Hours| Verifiable Certificate of Completion
4.9
View Course
#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 the below code and paste it into the VBA editor.
Code:
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 the Developer tab.
Step 2: Go to Insert & click on Module.
Step 3: Now, we can see that a 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 the RUN button on top. Or else you can press the 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.
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 the 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 examples and downloadable excel template. You can also go through our other suggested articles to learn more –
