Excel VBA: Unprotect a Workbook without using a Password

The sample workbook is protected with a password.

excel vba unprotect workbook without password

Example 1 – Copy the workbook to a New File to Unprotect it Using VBA

Steps:

The original file is in “xlsx” format.

Press F12 to open the Save As dialog box.
Select Save location.
Set these options:
- Filename: “UnprotectWorkbookWithoutPassword”.
- Save as Type: Excel 97-2003 Workbook (*.xls).
Click Save.

This will convert the Workbook from xlsx to xls format. A warning message is displayed.

Click Continue.

The Workbook is Protected: no Sheet can be added and the add option is Grayed Out.

To add a VBA code:

Go to the Developer tab >>> select Visual Basic.

In the Visual Basic window.

In Insert >>> select Module.

Enter the following code in the module.

Sub UnprotectWorkbookWithoutPassword()
    ActiveWorkbook.Sheets.Copy
    For Each AllSheets In ActiveWorkbook.Sheets
    AllSheets.Visible = True
    Next
End Sub

VBA Code Breakdown

A Sub Procedure UnprotectWorkbookWithoutPassword is called.
The Workbook is copied and then duplicated.
The Workbook is Unprotected.

Click Save.
Run the code.

You will be able to add Sheets to your Workbook.

Read More: Excel VBA: Unprotect All Sheets

Example 2 – Unprotect a Sheet in a Workbook Using Excel VBA

Unprotect the Workbook following Example 1.

To Unprotect the Sheets:

Steps:

Follow the procedure used in the first example to open the Module window.
Enter 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

A Sub Procedure UnprotectWorkbookWithoutPasswordWithProtectedSheets is created.
Variable types are declared.
If a run-time error occurs, the next statement will be executed.
A For Loop is used to run the code until we get the Password to Unprotect Sheet. The loop goes from 32 to 126 decimal values of the ASCII table.
The VBA Chr function is used to get the Character against the Decimal value according to the ASCII table.
The Password is found and disclosed (it isn’t the exact Password but a generic one).

Save and close the Module.

Go to the Developer tab >>> select Macros.

In the Macro dialog box:

Select “UnprotectWorkbookWithoutPasswordWithProtectedSheets”.
Click Run.

In the “Message Box”, click OK.

The Sheet is Unprotected.

Run the code for each Protected Sheet.

Things to Remember

The Second VBA code applies to protected Worksheets in an Unprotected Workbook.

Download Practice Workbook

Unprotect Workbook Without Password with VBA.xls

Get FREE Advanced Excel Exercises with Solutions!

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

4 Comments

Lyn Aug 17, 2022 at 3:06 AM

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.
- Reply
  Rafiul Haq Aug 21, 2022 at 3:52 PM
  
  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”.
Jenelle Castro May 3, 2023 at 9:44 PM

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

Excel VBA: Unprotect a Workbook without using a Password – 2 Examples

Example 1 – Copy the workbook to a New File to Unprotect it Using VBA

Example 2 – Unprotect a Sheet in a Workbook Using Excel VBA

Things to Remember

Related Articles

Leave a reply Cancel reply