Excel VBA: Unprotect Workbook without Password (2 Examples)

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

• 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

• 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!

Related Articles

• How to Unprotect Excel Workbook with Password
• How to Remove Password from Excel File
• How to Recover Excel File Password