Unlock Excel Sheets in Seconds with VBA
Last updated on August 28, 2024 By Victor Chan
What if I told you there’s a simple VBA hack that can unlock all your Excel sheets in seconds—even when you don’t know the passwords? In this article, I’m going to show you exactly what this secret VBA hack is and how it can save you hours of frustration.
You can get the full VBA code in Level 3.
But before we dive into VBA, let me show you a simple Excel method for managing multiple password-protected sheets — no coding required.
Download FREE Excel Workbook
Step 1: Sign up for free Click Here
Step 2: Log in for access Click Here
Step 3: Download file Unlock-Excel-Protection-with-VBA.zip
Are you new to Excel VBA Macros?
• Find out how to enable and disable macros in Excel with this tutorial
• Save time and effort by copying VBA macros from one workbook to another with these instructions
Level 1: Unprotect Multiple Sheets Using Excel’s Built-in Tools
The standard way to protect or unprotect a worksheet is to click on the Review tab, then choose Protect Sheet. Notice that when protection is enabled, you can’t insert rows and columns or edit cells.
To unprotect the sheet, click on Unprotect Sheet. Now you can insert columns and edit cells again.
But what if you need to unprotect multiple sheets? Do you have to go through the menu for each one separately? No! There’s a quicker way that works in Excel 365.
- Click on File.
- Click on Info. (#1 in screenshot)
- Under Protect Workbook, you can now unprotect each sheet, and you can see which sheets are still protected. (#2 in screenshot)
While this method is better, it’s still slow and tedious because you have to unprotect each sheet separately. Let’s do it better with VBA!
Level 2: Protect and Unprotect Multiple Sheets Using a Known Password (VBA)
What is VBA?
VBA stands for Visual Basic for Applications, and it’s a free built-in coding language that lets us automate Excel. Here’s how to get started.
- 1. Make sure the Developer tab is visible in your Ribbon. If not, right-click on the Ribbon and select Customize. Then check the Developer box and click OK.
- 2. Click on the Developer tab and then Visual Basic to open the Visual Basic Editor (VBE).
- 3. Insert a new code module in your workbook by right-clicking on any sheet, selecting Insert, and then Module.
- 4. Rename the module to “Protection” (optional).
Now, let’s look at the VBA code to protect and unprotect all worksheets with a password.
Protect All Worksheets in the Active Workbook
[VBA Code Box]
Sub Protect_All_Worksheets_ActiveWorkbook()
Dim ws As Worksheet
Dim pwd As String
pwd = "123" ' Password to protect sheets
' Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=pwd
Next ws
MsgBox "All worksheets in the active workbook" _
& " have been protected with the password '123'.", _
vbInformation, "Notification"
End Sub
This simple script loops through every worksheet in the active workbook and protects them with the password “123”. After the protection is applied, a message box will notify you.
Unprotect All Worksheets in the Active Workbook
[VBA Code Box]
Sub Unprotect_All_Worksheets_ActiveWorkbook()
Dim ws As Worksheet
Dim pwd As String
pwd = "123" ' Password to protect sheets
' Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:=pwd
Next ws
MsgBox "All worksheets in the active workbook" _
& " have been unprotected.", vbInformation, "Notification"
End Sub
This script does the reverse. It unprotects all worksheets in the active workbook using the same password and shows a message box to confirm the action.
Bonus Tip: Protect Sheets but Allow VBA Modifications
You can protect a worksheet while still allowing VBA code to make changes by setting the UserInterfaceOnly option to True. This can be very useful when you want to prevent users from editing the sheet manually but still allow your VBA code to modify it.
Try this in the Immediate Window (press Ctrl + G to bring it up):
[VBA Code Box]
ActiveWorkbook.Worksheets(1).Protect Password:="123", UserInterfaceOnly:=True
ActiveWorkbook.Worksheets(1).Range("A1").Value = "EDITED"
This allows VBA to modify the protected sheet while preventing manual edits.
Level 3: Remove Protection from Excel Worksheets Without Knowing the Password (Advanced VBA)
Now, what if you don’t know the password? Maybe you set the password months ago and forgot it, or you’re working on a spreadsheet from someone who left the company without sharing the password. This is where advanced VBA can help.
Want to dive deeper? Watch my full in-depth tutorial on YouTube for a step-by-step walkthrough—click here to check it out.
Overview
Our process for removing Excel protection has five stages. We can carry these out manually or automate them with VBA.
- Stage 1: Choose the Excel workbook to unlock.
- Stage 2: Make a copy of the workbook as a .ZIP file and extract its contents.
- Stage 3: Remove the workbook and worksheet protection from the extracted XML files.
- Stage 4: Repackage the edited XML files into a new .ZIP file.
- Stage 5: Clean up temporary files and convert the .ZIP file back into an Excel file.
Stage 1: Choose the Excel Workbook
In this stage, we’ll use a dialog box to select the Excel file from which we want to remove protection. The VBA code handles this selection process, ensuring that only XML-based Excel files (.xlsx, .xlsm, .xltx, .xltm) are allowed.
We choose these file types because they follow the Open XML format, which stores Excel data in a structured XML format within a ZIP archive.
This allows us to access and edit the underlying XML files directly, enabling us to remove password protections from the workbook and worksheets. Non-XML formats (e.g., .xls) do not use this structure, making it impossible to apply the same method.
Stage 2: Create a ZIP File and Extract XML Contents
Excel files are actually ZIP archives containing various XML files. To remove protection, we need to copy the Excel file as a ZIP file, extract the contents, and locate the XML files that control protection settings.
Stage 3: Remove Workbook and Worksheet Protection
This is the crucial step where we identify and remove the protection mechanisms embedded in the XML files. Excel files use XML to store data and settings, including password protection.
By editing the appropriate XML files, we can strip away the protection without needing to know the original password.
Since the process for removing worksheet protection, workbook protection, and file sharing passwords is similar, we’ll apply the same code to handle all three types of protection.
Our VBA code will:
- 1. Loop through each worksheet’s XML file in the extracted folder and remove the <sheetProtection> tag, which protects the worksheet.
- 2. Remove the <workbookProtection> tag from the main workbook XML file, which controls structural protections such as preventing users from adding, deleting, or moving worksheets.
- 3. Eliminate any <fileSharing> tags that impose file modification restrictions, preventing users from making changes to the file unless they know the password.
The code reads the XML files into memory, locates and removes the protection tags, and writes the cleaned content back to the XML files.
By handling all three types of protection, we ensure that the workbook and all its worksheets become fully unlocked.
Important: VBA cannot reliably bypass File Open passwords. If you need to keep your files more secure, it's recommended to use this option.
Stage 4: Repackage the Edited XML Files into a New ZIP File
After successfully removing protection from the XML files, we need to reassemble the pieces back into an Excel file format. This stage involves:
- 1. Creating a new, empty ZIP file. This file will serve as the container for our edited XML files.
- 2. Moving the edited XML files (including the workbook, worksheet, and other components) back into the ZIP file.
- 3. Ensuring that all files are compressed into the ZIP file correctly, so that when we rename the ZIP file back to its original Excel format, it will open and function as a regular Excel workbook.
By first reassembling the edited XML files into a new ZIP file, we ensure that everything is properly packaged back into the correct format before we convert it back into an Excel file in the final stage.
Stage 5: Clean Up Temporary Files and Convert the ZIP File Back into an Excel File
The final step in our VBA process is to clean up any temporary files and folders that were created during the process. This stage ensures that we leave no traces behind and that our file system remains tidy. And we rename the new ZIP file to an Excel file.
- 1. The temporary folder containing the extracted XML files is deleted.
- 2. The final ZIP file is renamed with the same extension as the original Excel file (e.g., .xlsx, .xlsm).
- 3. The user is notified that the protection has been successfully removed and that the workbook is ready for use.
This completes our journey through the removal of Excel protection. At the end of Stage 5, we emerge with a fully unlocked Excel file, free from any worksheet, workbook, or file modification restrictions.
Here is the complete VBA code, which you can copy and paste into your own workbook. Remember to use this responsibly.
[VBA Code Box]
'--------------------------------------------------------------------------------------------
' LAUNCH EXCEL
' www.launchexcel.com
'--------------------------------------------------------------------------------------------
' *** Remove_Excel_Protection Overview ***
' This module automates the removal of workbook and worksheet protection from an Excel file.
'
' It covers three types of protection:
'
' 1. WORKSHEET protection stops users from accidentally or deliberately changing, moving, or
' deleting data in a worksheet
'
' 2. WORKBOOK protection stops users from viewing hidden worksheets, renaming worksheets, or
' adding, moving, deleting, or hiding worksheets
'
' 3. FILE MODIFY protection makes the file Read Only unless you enter the password
'
' ### Subroutines and Functions ###
'
' - Sub RemoveProtection:
' - Prompts the user to select an Excel file to unlock.
' - Creates a temporary copy of the file, renames it to a zip file, and extracts its contents.
' - Loops through the XML files to remove protection tags from sheets, workbook, and file-sharing settings.
' - Repackages the modified files and saves the unlocked file with a new name.
' - Handles cleanup of temporary files and notifies the user upon completion.
'
' - Sub Process_Xml_File:
' - Takes an XML file path and protection tag as input.
' - Reads the XML content, removes the specified protection tag, and writes the modified content back to the file.
'
' - Function Remove_Protection_Tag:
' - Takes XML content and a tag name as input.
' - Searches for the specified protection tag in the XML content and removes it.
' - Returns the modified XML content.
'
' *** End of Module Overview ***
'--------------------------------------------------------------------------------------------
Sub Remove_Excel_Protection()
' Declare variables
Dim fileDialog As fileDialog
Dim selectedFileFullName As String
Dim selectedFilePath As String
Dim selectedFileName As String
Dim selectedFileExtension As String
Dim newFileFullName As Variant
Dim newFolderName As String
Dim newZipFilePath As Variant
Dim shellApp As Object
Dim fileSystemObject As Object
Dim worksheetXmlFileName As String
Dim fileNumber As Integer
Dim fileContent As String
Dim protectionTagStartPos As Double
Dim protectionTagEndPos As Double
Dim protectionTag As String
'---STAGE 1: CHOOSE WORKBOOK---
' Open dialog box to select a file
Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
fileDialog.AllowMultiSelect = False
fileDialog.Title = "Select file to remove protection from"
If fileDialog.Show = -1 Then
selectedFileFullName = fileDialog.SelectedItems(1)
' Check if the selected file is an XML-based Excel file
selectedFileExtension = LCase(Mid(selectedFileFullName, InStrRev(selectedFileFullName, ".") + 1))
If selectedFileExtension <> "xlsx" And _
selectedFileExtension <> "xlsm" And _
selectedFileExtension <> "xltx" And _
selectedFileExtension <> "xltm" Then
MsgBox "Please select an XML-based Excel file (.xlsx, .xlsm, .xltx, .xltm).", vbExclamation
Exit Sub
End If
Else
Exit Sub
End If
' Get folder path, file extension, and file name from the selected file
selectedFilePath = Left(selectedFileFullName, InStrRev(selectedFileFullName, "\"))
selectedFileName = Mid(selectedFileFullName, Len(selectedFilePath) + 1)
selectedFileName = Left(selectedFileName, InStrRev(selectedFileName, ".") - 1)
'---STAGE 2: COPY TO NEW .ZIP FILE AND EXTRACT---
' Create a unique folder name using the current date and time
newFolderName = "Temp--" & Format(Now, "yyyy-mm-dd--hh-nn-ss")
newFileFullName = selectedFilePath & newFolderName & ".zip"
' Copy and rename the original file to a zip file with a unique name
On Error Resume Next
FileCopy selectedFileFullName, newFileFullName
If Err.Number <> 0 Then
MsgBox "Unable to copy " & selectedFileFullName & vbNewLine & _
"Check that the file is closed and try again.", vbExclamation
Exit Sub
End If
On Error GoTo 0
' Create folder to unzip the file into
newZipFilePath = selectedFilePath & newFolderName & "\"
MkDir newZipFilePath
' Extract the files into the newly created folder
Set shellApp = CreateObject("Shell.Application")
shellApp.Namespace(newZipFilePath).CopyHere shellApp.Namespace(newFileFullName).items
'---STAGE 3: REMOVE PROTECTION---
' Loop through each XML file in the \xl\worksheets folder of the unzipped file
worksheetXmlFileName = Dir(newZipFilePath & "\xl\worksheets\*.xml*")
Do While worksheetXmlFileName <> ""
' Remove WORKSHEET protection by editing Worksheet XML
Process_Xml_File newZipFilePath & "xl\worksheets\" & worksheetXmlFileName, _
"sheetProtection"
worksheetXmlFileName = Dir
Loop
' Remove WORKBOOK protection by editing Workbook XML
Process_Xml_File newZipFilePath & "xl\workbook.xml", "workbookProtection"
' Remove FILE MODIFY protection by editing Workbook XML
Process_Xml_File newZipFilePath & "xl\workbook.xml", "fileSharing"
'---STAGE 4: ADD EDITED XML FILES TO NEW .ZIP FILE---
' Create a new empty ZIP file, replacing the previously copied file
' which was a temporary renamed version of the original Excel file
Open newFileFullName For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
' Move the files into the zip file
shellApp.Namespace(newFileFullName).CopyHere _
shellApp.Namespace(newZipFilePath).items
' Wait until compressing is complete
On Error Resume Next
Do Until shellApp.Namespace(newFileFullName).items.Count = _
shellApp.Namespace(newZipFilePath).items.Count
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
'---STAGE 5: CONVERT TO EXCEL AND CLEANUP
' Delete the temporary files and folders created during the subroutine
Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
fileSystemObject.DeleteFolder selectedFilePath & newFolderName
' Rename the final zip file back to an Excel file
Name newFileFullName As selectedFilePath & selectedFileName & _
"--" & Format(Now, "yyyy-mm-dd--hh-nn-ss") & "." & selectedFileExtension
' Notify the user that protection has been removed
MsgBox "The workbook and worksheet protection passwords have been removed.", vbInformation
End Sub
' Subroutine to handle reading, modifying, and saving XML files
Sub Process_Xml_File(ByVal xmlFilePath As String, ByVal tagName As String)
Dim fileNumber As Integer
Dim fileContent As String
' Step 1: Read the text of the XML file into a variable
fileNumber = FreeFile
Open xmlFilePath For Input As fileNumber
fileContent = Input(LOF(fileNumber), fileNumber)
Close fileNumber
' Step 2: Remove the protection settings from the XML content
fileContent = Remove_Protection_Tag(fileContent, tagName)
' Step 3: Write the modified content back to the XML file
fileNumber = FreeFile
Open xmlFilePath For Output As fileNumber
Print #fileNumber, fileContent
Close fileNumber
End Sub
' Function to remove protection tags from XML content
Function Remove_Protection_Tag(ByVal xmlContent As String, ByVal tagName As String) As String
Dim tagStartPos As Double
Dim tagEndPos As Double
Dim tag As String
tagStartPos = InStr(1, xmlContent, "<" & tagName)
If tagStartPos > 0 Then
tagEndPos = InStr(tagStartPos, xmlContent, "/>") + 2
tag = Mid(xmlContent, tagStartPos, tagEndPos - tagStartPos)
xmlContent = Replace(xmlContent, tag, "")
End If
Remove_Protection_Tag = xmlContent
End Function
Recap of the 5 Stages
- Stage 1: Choosing the Workbook – The user selects an Excel file via a file picker dialog box. The code ensures that the file is XML-based (i.e., .xlsx, .xlsm, .xltx, .xltm) and can be edited using this method.
- Stage 2: Copying to a New ZIP File and Extracting Contents – The code copies the Excel file, renames it as a ZIP file, and extracts its contents into a temporary folder.
- Stage 3: Removing Protection – The code loops through the extracted XML files to remove protection tags from the worksheets (sheetProtection), workbook (workbookProtection), and file sharing protection (fileSharing). The Process_Xml_File subroutine reads the XML content, modifies it, and writes the updated content back to the XML file.
- Stage 4: Repackaging into a New ZIP File – The code creates a new ZIP file, adds the modified XML files to it, and waits until the process is complete.
- Stage 5: Cleanup and Conversion – The temporary files and folders created during the process are deleted, and the ZIP file is renamed back to an Excel file with the original extension. Finally, a message box confirms that protection has been removed.
Conclusion
Now you have a powerful tool to unlock Excel sheets and remove protection—whether you know the password or not. With this VBA solution, you can take control of your workbooks like never before.
But this is just the beginning. Excel is packed with features and hidden capabilities that most users never tap into.
Learning VBA is your gateway to unlocking Excel’s full potential, and it can truly transform the way you work with data. Don’t be like most people who stick to slow manual methods—embrace the power of automation and coding in Excel!
To master Excel VBA from the ground up, join my online course, “Launch Excel Macros & VBA School”.
My course has helped over 500 students become proficient in VBA, starting from the basics and building up to advanced techniques. It’s designed to be structured, engaging, and practical, so you can apply what you learn right away.
Click here to learn more and start your journey into VBA today.
Happy Excelling!
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.