In this article, we will show you 2 examples of how to Unprotect Excel Workbook without a Password using VBA. We have got a Protected Workbook with a Password. We can’t add a Worksheet to that Workbook, we’ll unlock it without inputting the Password using Excel VBA. Our dataset has 3 columns: “First Name”, “Last Name” and “Email”.
Excel VBA Unprotect Workbook Without Password: 2 Suitable Examples
1. Copy to New File to Unprotect Workbook without Password Using VBA
In this method, we’ll copy the contents and create a new Excel Workbook to Unprotect it. Our Workbook can be in xlsx or xls format for the first method. However, we’re going to Save our xlsx Workbook as xls, and then we’ll input our VBA codes.
Steps:
Here, our original file is in “xlsx” format.
- Firstly, press F12 to bring up the Save As dialog box.
- Secondly, select Save location.
- Thirdly, set these options –
- Filename: “UnprotectWorkbookWithoutPassword”.
- Save as Type: Excel 97-2003 Workbook (*.xls).
- Finally, press Save.
This will convert our Workbook from xlsx to xls format. Moreover, a warning message will appear. This will tell us that if we save xls as xlsx, a few features may not be available.
- Press Continue.
We can see that our Workbook is Protected. We can’t add any Sheet to the Workbook. The add option is Grayed Out.
Now, we’ll add VBA code. To do that –
- Firstly, from the Developer tab >>> select Visual Basic.
This will bring up the Visual Basic window.
- Secondly, from Insert >>> select Module.
We’ll type our code in this Module window.
- Thirdly, type the following code.
Sub UnprotectWorkbookWithoutPassword()
ActiveWorkbook.Sheets.Copy
For Each AllSheets In ActiveWorkbook.Sheets
AllSheets.Visible = True
Next
End Sub
VBA Code Breakdown
- Firstly, we’re calling our Sub Procedure UnprotectWorkbookWithoutPassword.
- Secondly, we’re copying our Workbook.
- Finally, we’re duplicating it.
- This process will make the Workbook Unprotected.
- Then Save it.
- Finally, put the cursor inside the code and Run the code.
After executing the code, we will be able to add Sheets to our Workbook. Thus, we’ve Unprotected our Workbook without a Password using VBA code.
Read More: How to Unprotect Excel Workbook without Password
2. Unprotect Sheet in Workbook without Password Using Excel VBA
Now, if the Sheets are Protected along with the Workbook then we need to follow this method. Firstly, we’ll need to Unprotect the Workbook following method 1 and then Unprotect the Sheets.
Here, we can see our Sheet1 is Protected with a Password.
Steps:
- Firstly, following the first method, bring up the Module window.
- Secondly, type this code.
Sub UnprotectWorkbookWithoutPasswordWithProtectedSheets()
Dim x1 As Integer, x2 As Integer, x3 As Integer
Dim x4 As Integer, x5 As Integer, x6 As Integer
Dim x7 As Integer, x8 As Integer, x9 As Integer
Dim x10 As Integer, x11 As Integer, x12 As Integer
On Error Resume Next
For x1 = 65 To 66: For x2 = 65 To 66: For x3 = 65 To 66
For x4 = 65 To 66: For x5 = 65 To 66: For x7 = 65 To 66
For x8 = 65 To 66: For x9 = 65 To 66: For x10 = 65 To 66
For x11 = 65 To 66: For x12 = 65 To 66: For x6 = 32 To 126
ActiveSheet.Unprotect Chr(x1) & Chr(x2) & Chr(x3) & _
Chr(x4) & Chr(x5) & Chr(x7) & Chr(x8) & Chr(x9) & _
Chr(x10) & Chr(x11) & Chr(x12) & Chr(x6)
If ActiveSheet.ProtectContents = False Then
MsgBox "Password is " & Chr(x1) & Chr(x2) & _
Chr(x3) & Chr(x4) & Chr(x5) & Chr(x7) & Chr(x8) & _
Chr(x9) & Chr(x10) & Chr(x11) & Chr(x12) & Chr(x6)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
VBA Code Breakdown
- Firstly, we’re calling our Sub Procedure UnprotectWorkbookWithoutPasswordWithProtectedSheets.
- Secondly, we’re declaring our variable types.
- Thirdly, we’re telling it when a run-time error occurs, the next statement will execute.
- Later, used For Loop to run the cord until we get the Password to Unprotect Sheet. To go through all possible characters of the keyboard we run the loop from 32 to 126 decimal values of the ASCII table.
- Next, used the VBA Chr function to get the Character against the Decimal value according to the ASCII table.
- Then, we’re finding the Password.
- Finally, it will show a Password. However, this will not be the exact Password but a generic one.
- Thirdly, we’ll Save and close this Module.
Now, we’ll Run this code.
- Firstly, from the Developer tab >>> select Macros.
The Macro dialog box will appear.
- Secondly, select our Macro: “UnprotectWorkbookWithoutPasswordWithProtectedSheets”.
- Finally, press Run.
After a few seconds, a “Message Box” will appear.
- Press OK.
Now, we can see that our Sheet is Unprotected. We need to run this for each Protected Sheet.
In conclusion, we’ve shown you to Unprotect Sheets in a Workbook without a Password.
Read More: Excel VBA: Unprotect Workbook with Password
Things to Remember
- The first code can be run in xlsx format, converting to xls format is optional. However, for the second method, it is a must, if you’re using Excel 2013 or newer.
- The Second VBA code will hang Excel if the Workbook is Protected. So, before executing this code, we need to make sure only the Worksheet is Protected and Workbook is Unprotected.
- For Unprotecting the Sheets in the second method, we need to run the code for each Sheet individually.
Download Practice Workbook
Conclusion
We’ve shown you 2 Excel VBA Macros to Unprotect Workbook without Password. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!
I can’t open the password protected file because I’ve forgotten my password. Therefore I can’t open the developer Alt+F11 module. I need code that will run from another workbook that will open the workbook that is password protected and continue running open the file as read only if nothing else. I’ve tried this process from within a separate workbook but I still can’t get the code to complete.
Thank you Lyn for your comment.
You can try the ZIP method to remove password from the Excel file.
This may not work with Excel 365 version and you will need to use an “Excel password recovery utility”.
Do you have VBA to unlock multiple password protected files at once? I have a spreadsheet that is locked with about 100 sheets in it and the person with the password no longer works with us. Going one at a time seems exhausting, not to mention time consuming. Was hoping to find a code that will do them all at once. Nothing I’ve found seems to work.
Hello JENELLE CASTRO,
Thanks for your question. If you have the same password in all the files placed in a certain folder, you can apply the following VBA code.
Make necessary adjustments in the code in the Path and Password sections. I hope this is the solution which you are looking for.
Regards,
Naimul Hasan Arif