There were times when you wished to see the full contents of an Excel worksheet’s cell, but the sheet was protected and hence you could not drag the cell larger to see its content. Maybe you want to see the cell’s formula but was not allowed to do so by your less-than helpful co-worker. So what do you do when you hit a protected worksheet? Here’s the quick and easy way to make your life easier. No rocket science, just plain old cracking routines to crack the sheet by brute force.
Step 1. Press ALT + F11. Or, you can click on View Code in Developers Tabs. You should see this screen with the white editor area. If not, you may need press F7 (View code) to switch to the the white editor area code view.
Step 2. Paste the following into the white editor space area:
Sub PasswordBreaker() 'Breaks worksheet password protection. 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
You should the following screen:
Step 3: Once you have pasted the code, simply just click on the Run Button (green triangular arrow) or press F5.
Note: This will take a moment as the computer tries to crack the spreadsheet. When you see the message box “One usable password is : [some characters]”, the spreadsheet had been cracked successfully. Save this excel file with a new filename, maybe with the “(unprotected)” post-fix to denote that this version had been unprotected.
Have fun with your unprotected spreadsheet! Why not bookmark this page so you can come back another time when you hit the same problem again?
And while you are at it, why not click “Like” below to keep in touch with Gleescape? 🙂