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”
Also Read: Adding A ToolTip To Any Cell In Excel
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.
Private Sub Lock_Document_With_Psssword() Dim App_Name As String Dim User_pwd As String 'Find Appliction Name App_Name = VBA.Trim(VBA.UCase(Application.Name)) 'Get Password from User User_pwd = InputBox("Enter the Password to Lock") 'Check whcih application the Password had to be applied 'Code for Excel Document If App_Name = "MICROSOFT EXCEL" Then ThisWorkbook.Password = User_pwd End If 'Code for Word Document If App_Name = "MICROSOFT WORD" Then ThisDocument.Password = User_pwd End If 'Code for PPT Document If App_Name = "MICROSOFT POWERPOINT" Then ActivePresentation.Password = User_pwd End If 'Terminate Process MsgBox "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.
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.
Also Read: VBA Message Box Set No as Default Button
Lock Editing Excel using VBA?
Below VBA code is used to unlock and enable editing for specific cells and lock all remaining cells.
Private Sub UnLock_And_Protect_Remaining_Cells() Dim User_pwd As String 'Get Password from User User_pwd = InputBox("Enter the Password to Lock") 'Unprotect Sheet Before Editing ThisWorkbook.Sheets(1).Unprotect Password:=User_pwd 'UnLock Cells, Range, Columns or Rows ThisWorkbook.Sheets(1).Cells(1, 6).Locked = False ThisWorkbook.Sheets(1).Range("A2:C3").Locked = False ThisWorkbook.Sheets(1).Columns("D:E").Locked = False ThisWorkbook.Sheets(1).Rows(5).Locked = False 'Activate Protection for Locked Cells ThisWorkbook.Sheets(1).Protect Password:=User_pwd End Sub
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.