Excel VBA: How to Unprotect Excel Sheet Without Password

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to unprotect an Excel sheet without a password with the help of VBA, then you will find this article useful. Sometimes you may forget the password with which you have protected your sheet, and to solve this problem you can explore this article.


Unprotect Excel Sheet Without Password Using VBA: 3 Simple Steps

Here, we have the following dataset containing the records of sales and costs of some of the products of a company. But the sheet is protected with a password so we cannot make any changes here. To unprotect this sheet in the following steps we will show the ways to do this job easily with a VBA code.

unprotect Excel sheet VBA without password

We have used Microsoft Office 365 version here, you can use any other version according to your convenience.


Step-01: Opening Visual Basic Editor Window

Here, we have a sheet VBA where we have some records of the products. And we have protected it with a password.

unprotect Excel sheet VBA without password

So, for trying to make any changes we are getting the following message.

opening Visual Basic Editor

➤ To unprotect this sheet with the help of a VBA code right-click on the sheet name and then select the View Code option.

opening Visual Basic Editor

After that, the Visual Basic Editor window will open up.

opening Visual Basic Editor


Step-02: Typing VBA Code to Unprotect Excel Sheet without Password

➤ Type the following code

Sub unprotect_without_pass()
Dim digit1, digit2, digit3, digit4, digit5, digit6, _
digit7, digit8, digit9, digit10, digit11, digit12 As Integer
If ActiveSheet.ProtectContents Then
On Error Resume Next
For digit1 = 65 To 66: For digit2 = 65 To 66: For digit3 = 65 To 66
For digit4 = 65 To 66: For digit5 = 65 To 66: For digit6 = 65 To 66
For digit7 = 65 To 66: For digit8 = 65 To 66: For digit9 = 65 To 66
For digit10 = 65 To 66: For digit11 = 65 To 66: For digit12 = 32 To 126
ActiveSheet.Unprotect Chr(digit1) & Chr(digit2) & Chr(digit3) & _
Chr(digit4) & Chr(digit5) & Chr(digit6) & Chr(digit7) & Chr(digit8) & _
Chr(digit9) & Chr(digit10) & Chr(digit11) & Chr(digit12)
Next digit12: Next digit11: Next digit10: Next digit9: Next digit8: Next digit7
Next digit6: Next digit5: Next digit4: Next digit3: Next digit2: Next digit1
MsgBox "One usable password is " & Chr(digit1) & Chr(digit2) & _
Chr(digit3) & Chr(digit4) & Chr(digit5) & Chr(digit6) & Chr(digit7) & _
Chr(digit8) & Chr(digit9) & Chr(digit10) & Chr(digit11) & Chr(digit12)
End If
End Sub

Here, we have declared digit1, digit2, digit3, digit4, digit5, digit6, digit7, digit8, digit9, digit10, digit11, and digit12 As Integer.
The IF-THEN statement will ensure if the sheet is protected or not and if it is protected then the following operation will continue. Then we have used 12 FOR-NEXT loops for assigning 12 ranges of values to our declared digits and these loops will work as nested loops to unprotect the sheet with the help of these digits and the CHR function will convert them to strings.
Finally, a message box (MsgBox) will appear with a randomly generated password with the help of this code.

unprotect Excel sheet VBA without password


Step-03: Running Code to Unprotect Excel Sheet without Password

➤ Press F5.
This code will take some time to run.
After that, the sheet will be unprotected with the following message box containing a randomly generated password.

running code

So, we can easily change the values of this dataset and convert Strawberry to Orange here.

running code

One thing is to remember that this code will require huge time to run. And so it is better to do this operation on a spare PC.

Read More: Excel VBA: Unprotect All Sheets


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Download Workbook


Conclusion

In this article, we tried to cover the steps to unprotect an Excel sheet without a password with the help of VBA. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo