VBA can remove a password from Excel worksheet protection or workbook-structure protection when you already know the password; it cannot reveal or bypass an unknown password used to encrypt a workbook before it opens. The macro below tries one supplied password on every sheet and on the workbook structure, so make a backup copy first and use it only on a file you are authorized to change.
What kind of Excel password are you dealing with?
Excel uses the word “password” for different protections, and the distinction determines whether VBA can help. A workbook may be protected in one or more of these ways:
- Worksheet protection: Restricts actions such as editing locked cells, changing formatting, or inserting rows. It is controlled separately for each sheet.
- Workbook-structure protection: Restricts changes to the sheet tabs, such as adding, deleting, moving, hiding, or renaming sheets.
- Password to open: Encrypts the workbook file so Excel asks for a password before showing its contents.
- Password to modify: Lets someone open a file, often as read-only, but asks for a password to make changes. This is different from sheet protection.
The code here addresses the first two kinds, not file encryption. A workbook can have both protected sheets and protected structure, so removing one layer does not necessarily remove the other. A message that says a sheet is protected is not the same problem as a prompt that appears before the workbook opens.
VBA code for known sheet and structure passwords
This macro asks for a password and tries it on every worksheet and on the active workbook’s structure. It is suitable only when the same known password was used for those protections. If different sheets have different passwords, this single-password version will report the failures rather than remove those protections.
Option Explicit
Sub RemoveKnownProtection()
Dim wb As Workbook
Dim ws As Worksheet
Dim pwd As String
Dim failures As String
' Target the workbook that is active when the macro runs.
Set wb = ActiveWorkbook
If wb Is Nothing Then Exit Sub
' Ask for a password you already know; cancel or leave blank to stop.
pwd = InputBox("Enter the known protection password:", _
"Remove known protection")
If Len(pwd) = 0 Then Exit Sub
' Try each sheet separately and record any unsuccessful attempt.
For Each ws In wb.Worksheets
On Error Resume Next
ws.Unprotect Password:=pwd
If Err.Number <> 0 Then
failures = failures & vbCrLf & ws.Name & ": " & Err.Description
Err.Clear
End If
On Error GoTo 0
Next ws
' Try to remove workbook-structure protection with the same password.
On Error Resume Next
wb.Unprotect Password:=pwd
If Err.Number <> 0 Then
failures = failures & vbCrLf & "Workbook structure: " & Err.Description
Err.Clear
End If
On Error GoTo 0
If Len(failures) = 0 Then
MsgBox "The attempted sheet and structure protections were removed.", _
vbInformation
Else
MsgBox "Some protection could not be removed. Check the password " & _
"and the affected items:" & failures, vbExclamation
End If
End Sub
The ampersands and less-than sign shown as HTML entities inside the code block represent the ordinary VBA operators and symbol: in the VBA editor, use & for string concatenation and <> for “not equal to.” The code is HTML-escaped here so it displays correctly as a web page. Do not type the entity spellings into the editor; type the actual characters.
How to run the macro in Microsoft 365 for Windows
Save a separate copy of the workbook before running any macro. VBA changes cannot be undone with Ctrl+Z, and a macro can affect more than the selected cell. If a macro is stored in the workbook itself, save that working copy as an Excel Macro-Enabled Workbook (.xlsm); an .xlsx file cannot store VBA code. You can also run a macro stored in a trusted personal macro workbook, with the intended target workbook active.
- Open the copy in the Excel desktop app and click the workbook you intend to change so it is the active workbook.
- Open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, then paste the code into the module.
- Return to Excel. Press Alt+F8, select
RemoveKnownProtection, and choose Run. Enter the known password when prompted. - Check each sheet and, where relevant, try the sheet-tab actions that were previously restricted. Save the modified copy under a clear filename.
On Mac, VBA is available in the Excel desktop app, not Excel for the web. The Visual Basic Editor shortcut is commonly Option+F11, sometimes with Fn as well depending on the keyboard; macro-list shortcuts can also vary by keyboard settings. Use the Excel menu or the Developer tab if a shortcut is intercepted. Excel for the web cannot run VBA, so open the file in desktop Excel to use this macro.
Why an open-password cannot be removed by this macro
A password-to-open prompt appears before Excel has opened and exposed the workbook’s sheets to VBA. For an encrypted file, Excel needs the correct password to decrypt the contents first. A macro inside that unopened file cannot execute, inspect its sheets, or remove its encryption. This is why code that calls Worksheet.Unprotect is not a solution to a lost file-open password.
If you know the open password and can open the file, remove it through Excel’s interface instead. In Microsoft 365 for Windows, open the workbook and go to File > Info > Protect Workbook > Encrypt with Password. Clear the password in the dialog, confirm, then save the workbook. Menu wording can vary slightly between builds. Keep the original until you have closed and reopened the saved copy successfully. A password to modify may require different save or permission steps; do not assume the sheet-protection macro changes it.
Common problems and what the results mean
- The macro says some items could not be changed: The password may be wrong, or the workbook may use different passwords for different sheets and for its structure. The macro tries the same entry on each item and reports unsuccessful attempts.
- Some sheets changed before a failure appeared: The macro handles each sheet separately. A successful attempt may already have removed protection from some sheets before another item fails. Review the workbook and save only if that partial result is what you want.
- The macro is missing from the list: Confirm that the code is in a standard module and that macros are permitted for this trusted file. Do not enable all macros globally just to run an unfamiliar file.
- The structure is still protected: Sheet protection and workbook-structure protection are separate. The macro attempts both, but one password may not fit both layers.
- The workbook opens read-only or asks for a password to modify: That prompt concerns editing permissions or file-saving behavior, not necessarily worksheet protection. Make sure you have permission to edit and save a new copy rather than overwriting a source file.
Worksheet protection is mainly intended to prevent accidental edits; it should not be treated as strong confidentiality for sensitive data. File encryption is a separate, stronger barrier. If the workbook contains confidential information, manage access to the file itself and avoid distributing an unencrypted copy simply because a sheet is protected.
Safe alternatives when the password is unknown
If you do not know the password, the safest route is to ask the workbook owner or the person who set the protection. Check approved password managers, handover notes, and an organization’s documented recovery process. If the file is stored in OneDrive or SharePoint, check version history or contact the administrator about an authorized earlier version. A prior copy may have the required contents without needing to defeat a protection mechanism.
For a file-opening password that has been lost, look for an authorized unencrypted backup or ask the creator for a new copy. Microsoft support generally cannot retrieve a forgotten password for an encrypted file. Avoid uploading confidential workbooks to online “unlocking” sites: the service may receive the entire file, including hidden sheets and business data. Be cautious with downloadable password-recovery tools too; verify their source and your organization’s policy before using them. No recovery method can promise success for every kind of protection or file.
Frequently Asked Questions
Can VBA remove an Excel password without knowing it?
No. The macro above uses a password supplied by the user; it does not discover, crack, or bypass an unknown password. For an encrypted password-to-open file, Excel must decrypt the file before VBA inside it can run.
Does this remove protection from every worksheet?
It attempts every worksheet using the same entered password, then attempts workbook-structure protection. It can succeed only where that password matches. Sheets with separate passwords need their own known passwords and an appropriate authorized process.
Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in the desktop version of Excel for Windows or Mac, subject to your organization’s macro settings.
Will removing sheet protection delete data or formulas?
The macro calls the unprotect methods; it does not intentionally delete cell contents or formulas. Still, make a backup, because it is a change that Ctrl+Z cannot reverse and other workbook features may be affected by later editing.
What if I know the password to open the workbook but not the sheet password?
Opening the file proves only that you can decrypt and view it; it does not supply the separate password used for a protected sheet. Ask the owner for that password or use an authorized earlier version rather than treating the two protections as interchangeable.
Related
- Scatter Plot in Excel With Two Variables
- Scatter Plot Excel: How to Label Every Point With Names
- Scatter Plot in Excel: Show Multiple Variables Clearly
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Tiny Toggle: Make a Small On/Off ButtonIf you mean a tiny on/off control that runs a macro, Excel has no built-in feature called a…
- Excel VBA ABS Function: Syntax and ExamplesIn Excel VBA, use the built-in Abs function to return a number’s absolute value: Abs(number). For example, Abs(-12.5)…
- Excel VBA Sort Custom Order: How to Set It UpTo sort rows in a custom sequence with Excel VBA, add a sort field with the CustomOrder argument,…
- Excel VBA AutoFilter Sort: Filter and Sort Data RowsTo filter and sort rows with Excel VBA, apply AutoFilter to the data range, then configure that range’s…
- VBA GoTo: Syntax, Examples, and Safer AlternativesIn VBA, GoTo is a statement, not a function: it transfers execution to a labeled line in the…
- Excel VBA Data Entry: Forms, Validation, and MacrosExcel VBA data entry uses Visual Basic for Applications to collect, check, and write information in a workbook.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.