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.
Unprotect Specific Worksheet while opening Workbook

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! 

FREE Advanced Excel Ebook

Get This Amazing PRO Excel & Excel VBA Tips and Tricks Ebook worth $ 30 for FREE ! Apply Ready to Use Formulas and VBA code to your requirements and work faster and smarter!

Leave a Reply

Your email address will not be published. Required fields are marked *

Hey there, there are some great free eBooks available that can help you improve your skills in Excel, reporting, and accounting. Would you like to check them out?