Password Protect Excel 2010 & 2007

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.

1. Click on File -> Save As to view a Popup window for entering File Name.
2. In this popup window, to the left side of “Save” Command Button, there is a drop down named as “Tools”.
3. 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.

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.

Related: How to Strengthen the Protection by Encrypting Excel using VBA?

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.

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.

More Tricks:

• Read or Fetch Website Content through VBA to Excel
• Send Personalized Email to Multiple Recipients from Excel