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”.
Download Practice Workbook
2 Examples to Unprotect Workbook without Password Using VBA in Excel
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.
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.
- 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.
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.
- 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.
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.
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!