Protect and Unprotect (Without Password) a Workbook

puneet-gogia-excel-champs

- Written by Puneet

In Excel, you can protect and unprotect a workbook with a password. And that’s what we are going to discuss in this tutorial.

Apart from this, we will also discuss how to unprotect a workbook when you don’t remember the password, or you don’t the password.

Let’s get started.

Protect a Workbook

  1. Go to the Review Tab.
  2. Click on the “Protect Workbook”.
  3. Enter the password twice.
  4. Hit enter to protect the workbook.
protect-a-workbook

Unprotect a Workbook

  1. Go to the Review tab.
  2. Click on the Protect Workbook.
  3. Enter the password to specify.
  4. In the end, click OK to unprotect the workbook.
unprotect-a-workbook

If you enter the correct password, it unlocks the workbook. And if the password you have entered is incorrect, then it will show an error like below:

password-to-unlock-workbook

What IF I Don’t Remember (Or Don’t Know) the Password

There is a VBA code (Macro) that you can use to know the password if you don’t have it.

Sub PasswordBreaker()
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

To use this code:

  1. Go to the Developer Tab and click “Visual Basic” to open the visual basic editor. Or you can also use the keyboard shortcut Alt + F11.
    4-open-developer-tab
  2. After that, in the Visual Basic Editor, insert a new module.
    5-insert-a-new-module
  3. Once you insert a new module, paste the code into the code window on the right side of the module window.
    6-paste-code-in-new-module
  4. Now close the visual basic editor, go back to the developer tab, and click on the Macros button.
    7-close-the-visual-basic-editor
  5. From there, select the macro and click on the Run button.
    8-select-the-macro

The moment you click OK, the code changes the applied password to “AAAAAAAAAAA”.

code-changes-the-applied-password

And then, you can use this password to unprotect the workbook.

Protect Workbook While Saving It

Once you go to the File tab > Save As click the “Browse” option.

protect-workbook-while-saving-it

Now, locate the folder in the Save Dialog box where you want to save the file. And before you click Save, click on “Tools” and then “General Options”.

locate-the-folder

It will open a dialog box to apply the password to the file. Please enter the password to lock the file for opening and one password for modifying.

enter-password

Once you enter the passwords to it, and then click OK and then confirm the password by entering. Click OK to apply the password. With this, you must enter the specified password when you open the file.

confirm-password
Last Updated: October 04, 2023