Excel - A macro to unlock a password-protected sheet

Ask a question
It is possible to create a macro in an Excel sheet that unlock the sheet which is already protected by a password. Users can easily prepare a document in the sheet and then lock it again with a password. Sometimes the password-protected sheet cannot be opened. A specific command or keywords need to be typed in to create a macro in Excel. This can be done through the visual basic editor of Excel. Users can unlock password-protected sheets with a simple syntax. After unlocking, all the official calculations and tasks can easily be performed. This option will help the user to maintain privacy.


Issue


I need to create a macro that unlocks a password-protected sheet with a defined password, performs certain tasks and then locks it again with the same password.

Solution


The syntax is quite simple:
ActiveSheet.Unprotect ("MyPassWord")       
'instructions       
ActiveSheet.Protect ("MyPassWord")      

For more details, I suggest you type "worksheet.protect" and "worksheet.unprotect" in the help file of visual basic editor.

Also try this syntax:

ActiveSheet.Protect PassWord:="MyPassWord", DrawingObjects:=True,  _        
Contents:=True,  Scenarios:=True, AllowSorting:=True, AllowFiltering:=True,  _        
AllowUsingPivotTables:=True 

Note that


Thanks to Ivan-hoe for this tip on the forum.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team