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


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.

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.

excel vba unprotect workbook without password

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.

excel vba unprotect workbook without password

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.

excel vba unprotect workbook without password

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

excel vba unprotect 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:

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.

Code Module: excel vba unprotect workbook without password

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

excel vba unprotect workbook without password

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.

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!

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

2 Comments
  1. 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.

Leave a reply

ExcelDemy
Logo