Unprotect Specific Worksheet while opening Workbook

Learn how to Unprotect specific worksheet while Opening your Protected Workbook

This is can be extremely useful when you are working in collaboration

Suppose you have a workbook containing extremely sensitive information but it needs to be updated periodically, in this case using this Amazing VBA Code, you can unprotect Specific Worksheet when a workbook opens each time

If you are distributing workbooks with protected sheets, you likely get the workbooks back with the worksheets still protected.

Often, you need to unprotect the worksheets in a workbook before continuing your work.

If you find that you are continuously unprotecting worksheets, this macro may be just the ticket.

How it works (Protect a Worksheet on Workbook Close)

This code is triggered by the workbook’s Open event.

When you open a workbook, this event triggers, running the code within.

This macro automatically unprotects the specified sheet with the given password when the workbook is opened.

Ready to use/apply VBA Code (double click to select entire code)


Private Sub Workbook_Open()
'Step 1: Protect the sheet with a password
Sheets("Sheet1").Unprotect Password:="RED" 'change the name of the worksheet here instead of Sheet1 and also set your password
End Sub

The macro explicitly unprotects a worksheet named — Sheet1 in this case.

Then it passes the password required to unprotect the sheet.

Be aware that Excel passwords are case-sensitive, so pay attention to the exact password and capitalization that you are using.

How to use it? and Unprotect Specific Worksheet while opening workbook

To implement this macro, you need to copy and paste it into the Workbook_Open event code event code window.

Placing the macro here allows it to run each time you try to Open the workbook.

Activate the Visual Basic Editor by pressing ALT+F11
In the Project window, find your project/workbook name and click the plus sign next to it in order to see all the sheets.
Click ThisWorkbook.
Select the Open event in the Event drop-down list (see GIF animation below).
Type or paste the code in the newly created module, modifying the sheet name (if necessary) and the password. Note that you can unprotect additional sheets by adding additional statements.

Detailed Explanation:

As you can see in above GIF animation, I have created a brand new workbook and saved it as XLSM or XLSB file
This file has three sheets: Sheet1, Sheet2 and Sheet3
After that, I have opened Developer Tab and then Visual basic (ALT+F11) Then Select ThisWorkbook object to access VB Code window for the workbook
I pasted the code to unprotect Sheet1 every time this workbook reopens, please note that I have set unprotect password to “RED” and kept default sheet name
Before I close the workbook, I have set the password of each Sheet in the workbook (Sheet1, Sheet2, Sheet3) as “RED” You can see each I am not able to edit anything in all the sheets in the workbook
Then I close the workbook and reopen it
As you can see Sheet1 is unprotected automatically and ready for editing even though other two sheets are still protected (i.e. Sheet2 and Sheet3)
This way, you can automate repetitive task like Unprotect Specific Worksheet whenever you or someone else reopens this workbook while maintaining integrity of other sheets and workbook as a whole

Also check out my blog post on how to Protect a Specific Worksheet while closing a Workbook here

If you want to master Excel in Professional way, check out my FREE EBook available for download, this Ebook contains more of these ready to apply Excel VBA codes and formulas! (Just fill below form to get this amazing Ebook for FREE)

If you have any question or query regarding above code usage, let me know in the comments below!