How To Password Protect Excel & Word Documents?
The Article explains how to secure a document with built in options & then a corresponding VBA Code snippet.
There are different methods available with Microsoft Office version 2007 and above, for Password Protecting Excel, Word and PPT documents. Let’s see one by one.
Applicable To: MS Excel, Word or PowerPoint of MS Office 2007 and above.
Password Protect Excel 2010, 2007 & Above
Menu Navigation Path: File -> Save As -> Tools -> General Options -> Enter Password
Open the Document that you want to protect with a Password and follow the below steps.
- Click on File -> Save As to view a Popup window for entering File Name.
- In this popup window, to the left side of “Save” Command Button, there is a drop down named as “Tools”.
- Click on this “Tools” Drop down and choose General Option. This will open up a windows for user to enter the “Password to Open” and “Password to Modify”
Password Protect Excel Using VBA?
The below VBA code can be used in any of the above explained three documents. The same code is Aapplicable to Password Protect a Word Document also.Visual Basic1234567891011121314151617181920212223242526272829Private Sub Lock_Document_With_Psssword()Dim App_Name As StringDim User_pwd As String'Find Appliction NameApp_Name = VBA.Trim(VBA.UCase(Application.Name))'Get Password from UserUser_pwd = InputBox("Enter the Password to Lock")'Check whcih application the Password had to be applied'Code for Excel DocumentIf App_Name = "MICROSOFT EXCEL" ThenThisWorkbook.Password = User_pwdEnd If'Code for Word DocumentIf App_Name = "MICROSOFT WORD" ThenThisDocument.Password = User_pwdEnd If'Code for PPT DocumentIf App_Name = "MICROSOFT POWERPOINT" ThenActivePresentation.Password = User_pwdEnd If'Terminate ProcessMsgBox "Process Completed"End Sub
It is simple to use this option. But, is it safe to use this option? I leave that decision to the User. Google to find tools that can crack or recover these passwords and you will be able to come to a conclusion.
If you want to provide more protection, then you can choose to encrypt the documents with some available tools or the method discussed in this Related Topic below.
Also Read: Excel VBA - Merge Cells - Unmerge Range
Protect Excel Worksheet from Editing
Menu Navigation Path: Review -> Protect Sheet -> Enter Password
Pre-Requisite: Select Cell or Range -> Right Click -> Format Cells -> Protection -> Check “Protection”
With this option, the user can view the data in excel worksheet, but they will not be allowed to edit it. This option is provided to:
- Prevent user from accidentally modifying any data and
- Prevent unauthorized users to modify the document.
Lock Editing Excel using VBA?
Below VBA code is used to unlock and enable editing for specific cells and lock all remaining cells.Visual Basic123456789101112131415161718Private Sub UnLock_And_Protect_Remaining_Cells()Dim User_pwd As String'Get Password from UserUser_pwd = InputBox("Enter the Password to Lock")'Unprotect Sheet Before EditingThisWorkbook.Sheets(1).Unprotect Password:=User_pwd'UnLock Cells, Range, Columns or RowsThisWorkbook.Sheets(1).Cells(1, 6).Locked = FalseThisWorkbook.Sheets(1).Range("A2:C3").Locked = FalseThisWorkbook.Sheets(1).Columns("D:E").Locked = FalseThisWorkbook.Sheets(1).Rows(5).Locked = False'Activate Protection for Locked CellsThisWorkbook.Sheets(1).Protect Password:=User_pwdEnd Sub
Also Read: Excel VBA - Convert Column Letter to Number
Restricting Access at User Level
Menu Navigation Path: Review -> Protect Document -> Restricted Access
This option does not provide option to enter password, but, you enforce a user level restriction to Read, Modify and Copy an Excel, Word or PPT document.
Use the above navigation and set required access to the users on who can read and who can modify the document. With this option you can also define what are the options the user is authorized to do and also Manage the user credentials. Refer MSDN to learn more details on these topics.
That’s all about how to secure and password protect your Office Documents. If there are any other possible methods that is missed out here, post us in comments section.
- Read or Fetch Website Content through VBA to Excel
- Send Personalized Email to Multiple Recipients from Excel
Password Protect Excel 2010 & 2007