Excel VBA: Unprotect Workbook without Password (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


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.

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

Read More: Excel VBA: Unprotect All Sheets


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.

Read More: Excel VBA: How to Unprotect Excel Sheet Without 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 the 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 the Workbook without a Password. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!


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.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

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

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

    • Reply Avatar photo
      Naimul Hasan Arif May 7, 2023 at 12:20 PM

      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.

      Sub UnProtect_Multiple_Files()
      Dim FName As String
      Dim Path As String
      Dim FSearch As String
      
      Path = "C:\Users\Dell\Desktop\Arif\Update\"
      FSearch = "*.xls"
      FName = Dir(Path & FSearch)
      Do While FName <> ""
      Workbooks.Open FileName:=Path & FName
      Workbooks(FName).UnProtect "1234"
      Workbooks(FName).Save
      Workbooks(FName).Close
      FName = Dir()
      Loop
      End Sub

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo