Unprotect or Unlock a Sheet with a Password or Without a Password

- Written by Puneet

excelchamps-free-courses-puneet-gogia

If a sheet or a worksheet is protected, you can unprotect it with the password applied to it. In this tutorial, we will learn to unprotect the sheet, and we will also learn to use a VBA when you don’t remember the password to unlock the sheet.

Unprotect or Unlock a Sheet with a Password

  1. First, right-click on the Sheet Tab.
    1-unprotect-a-sheet
  2. After that, click on the “Unprotect Sheet” Option.
    2-click-on-unprotect-sheet-option
  3. Now, enter the password in the input bar.
    3-enter-password-to-unprotect
  4. In the end, click OK to unprotect the sheet.
    4-click-ok-to-unprotect

You need to follow these simple steps to unprotect a sheet or a worksheet. But if you don’t have the password, you forget the password. In that case, you need to use a different method.

Unlock a Sheet without a Password

As I said, you need to use a VBA code for this. And below is the code that you need to use.

Sub unlock_sheet_without_password()
'Add the code Visual Basic Editor and Run it.
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

Note: Before using this code, save a file backup. It sometimes takes time to get executed and break the password.

  1. Go to the developer tab and click on Visual Basic. And if you don’t have the developer tab, you must activate it first.
    5-click-on-visual-basic
  2. On the right side of the Window, click on the sheet name and insert a new module from there.
    6-click-on-sheet-name-and-insert-new-module
  3. You have the code window on the right side of your Window. Paste the code there.
    7-paste-the-code-here
  4. After that, click on the run button to run the code.
    8-run-the-code
  5. Ultimately, when you run the code, it shows you a cracked password in the message box which you can use to unprotect the sheet.

Points to Remember

  • Before using this code, save a file backup. It’s possible to get an Excel crash if you run this code.
  • If you are using Excel 365, or any version between 2013 – 2021, you must save the file in the XLS format first, and once you break the  
  • It works for a single sheet at a time. If you have multiple sheets, you need to run the code that many times.
Last Updated: August 10, 2023