Sometime we need to protect excel sheets to prevent from unauthorized editing but what if we forget password. There are two methods:
- Using zip tool like 7zip
- Excel VBA
Method 1: Using zip tool like 7zip:
- Download and install suitable 7Zip pack like 64 bit 32 bit from here
- Right click on Excel file and go to 7zip then open Archive
- Now Go to xl folder then worksheets there you will see sheet1.xml
- Right click on Sheet1.xml then click on edit
- Search for 'protection'
- Delete
tag. Tag starts with (
- Now Save File and then click ok on 7zip popup to modify excel file
- Now You can edit or make changes in excel file protection is removed
Method 2: Using Excel VBA:
1. Open your worksheet which has been protected before.
2. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA code: recover lost worksheet protection password
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Sub PasswordRecovery()Dim i As Integer, j As Integer, k As IntegerDim l As Integer, m As Integer, n As IntegerDim i1 As Integer, i2 As Integer, i3 As IntegerDim i4 As Integer, i5 As Integer, i6 As IntegerOn Error Resume NextFor i = 65 To 66: For j = 65 To 66: For k = 65 To 66For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126ActiveSheet.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 ThenMsgBox "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 SubEnd IfNext: Next: Next: Next: Next: NextNext: Next: Next: Next: Next: NextEnd Sub |
4. Then click F5 key to run this code, and a prompt box will pop out, click OK to close it, and your passwords of the protected worksheet will be canceled at once.
