You can use VBA’s ‘Workbook.Protect‘ method to protect an Excel workbook and prevent unauthorized users from viewing hidden worksheets, adding, renaming, moving, hiding, or deleting sheets.
Protecting a workbook means protecting the workbook’s structure, which is the organization and arrangement of the workbook’s elements, such as sheets, charts, and other objects.
You can protect a workbook with or without a password. If you only want authorized users to unprotect the workbook, you must protect it with a password.
Protecting a workbook without a password can be helpful in situations where you only want to prevent accidental changes or modifications to the workbook by yourself or someone else.
I recommend you create a backup of your workbook before implementing any password protection. By doing so, you can still access the unprotected version of the workbook if you forget the password or encounter any issues with the protected version.
In this tutorial, I will show you some scenarios where you can use VBA to protect a workbook, but before diving in, let’s learn about the ‘Workbook.Protect‘ method.
Workbook.Protect Method in VBA
The syntax of the ‘Protect’ method is as follows:
expression.Protect (Password, Structure, Windows)
Note: expression is a variable representing a ‘Workbook’ object.
The ‘Protect’ method has the following three optional arguments or parameters:
- Password: This is a string specifying a case-sensitive password for the workbook. If you omit this argument, you can unprotect the workbook without a password. Otherwise, you must set the password to unprotect the workbook. You can only unprotect the workbook if you remember the password.
A strong password at least eight characters long is essential, combining uppercase and lowercase letters, numbers, and symbols. For example, a strong password could be Z3dfh#ap4.
It is crucial to remember your password. If you forget it, Microsoft cannot recover it. Please write the password and store it securely away from the workbook it protects.
- Structure: ‘True’ to protect the workbook’s structure (the sheets’ relative position), preventing users from adding, deleting, hiding, or renaming worksheets. ‘False’ is the default value.
- Windows: ‘True’ to protect the workbook windows, preventing users from resizing, moving, or closing them. If this argument is omitted, the windows aren’t protected. Please note that this feature is no longer supported in Single Document Interface (SDI) versions of Excel, such as Excel 365.
Also read: Unprotect Excel Sheet Without Password
Protect the Current Workbook Without Password
Below is the VBA code that would protect the current workbook without using a password.
Sub ProtectCurrentWorkbookWithoutPassword()
' Protect the structure of the currently active workbook
ThisWorkbook.Protect
End Sub
This macro applies the .Protect method to ThisWorkbook, which refers to the workbook containing this VBA code.
By default, this will protect the structure of the workbook. This means users will be unable to make changes like adding, deleting, or renaming worksheets.
Note that while the workbook is protected, this protection can be easily removed by any user through the Excel interface, since no password is set.
If you ever need to add password protection, you can modify the method to include a password parameter (as shown in the next section).
Protect the Current Workbook With Password
Below is the VBA code that would protect the current workbook while also using a password:
Sub ProtectCurrentWorkbookWithPassword()
' Protect the structure of the currently active workbook with a password
ThisWorkbook.Protect Password:="Z@fh#ap4"
End SubThis macro applies the .Protect method to ThisWorkbook, which is the workbook containing the VBA code.
It sets a password (“Z3fh#ap4”) for the protection and ensures that the structure of the workbook is protected. This means users will be unable to add, delete, rename, hide, or unhide worksheets unless they know the password.
Remember, it’s crucial to keep the password in a safe place. If you forget the password, there’s no easy way to unprotect the workbook.
Also read: Create New Workbook Using VBA in Excel
Protect a Specific Open Workbook
Here’s an example code snippet that protects the ‘Student Population.xlsx’ workbook in the ‘Workbooks’ collection:
Sub ProtectSpecificWorkbook()
' Protect the workbook named "Student Population.xlsx" without a password
Workbooks("Student Population.xlsx").Protect
End Sub
This macro applies the .Protect method to the workbook named “Student Population.xlsx”. It ensures that the structure of the specified workbook is protected, meaning that users won’t be able to add, delete, rename, hide, or unhide sheets within this workbook.
If you want to protect this workbook and also apply a password to it, you can use the below code:
Sub ProtectSpecificWorkbook()
' Protect the workbook named "Student Population.xlsx" without a password
Workbooks("Student Population.xlsx").Protect Password:="ThePassword"
End SubMake sure that a workbook with the name “Student Population.xlsx” is open when this macro is run; otherwise, an error will occur.
Protect All Open Workbooks
To protect all the open workbook, you need to go through all the open workbooks one by one and protect them.
The below VBA code will protect all the open workbooks.
Sub ProtectOpenWorkbooks()
' Declare a Workbook variable for iteration
Dim wb As Workbook
' Loop through each open workbook and Protect it
For Each wb In Workbooks
wb.Protect
End If
Next wb
End Sub
This VBA macro code iterates through all workbooks that are currently open in Excel. For each workbook, the macro applies the .Protect method, thereby protecting the workbook.
In case you want to add a password to each workbook while protecting them, you can use the below code:
Sub ProtectOpenWorkbooks()
' Declare a Workbook variable for iteration
Dim wb As Workbook
' Loop through each open workbook and Protect it
For Each wb In Workbooks
wb.Protect Password:="ThePassword"
End If
Next wb
End SubReplace the text “ThePassword” in the code with the password that you want to use.
Also read: How to Close Workbook Using VBA
Protect Only Open Macro-Enabled Workbooks
You can adapt the following example code to protect only open macro-enabled workbooks:
Sub ProtectMacroEnabledWorkbook()
' Declare a Workbook variable for iteration
Dim wb As Workbook
' Loop through each open workbook
For Each wb In Workbooks
' Check if the workbook has a VBA project (is macro-enabled)
If wb.HasVBProject Then
' Protect the macro-enabled workbook
wb.Protect
End If
Next wb
End SubIn this macro, the For Each loop iterates through all open workbooks.
The If wb.HasVBProject Then statement checks if the workbook contains a VBA project, which is a characteristic of macro-enabled workbooks (.xlsm files typically). If a workbook is macro-enabled, it gets protected.
In case you want to protect the macro-enabled workbooks with a password, you can use the code below:
Sub ProtectMacroEnabledWorkbook()
' Declare a Workbook variable for iteration
Dim wb As Workbook
' Loop through each open workbook
For Each wb In Workbooks
' Check if the workbook has a VBA project (is macro-enabled)
If wb.HasVBProject Then
' Protect the macro-enabled workbook with password
wb.Protect Password:= "Z3fh#ap4"
End If
Next wb
End SubAlso read: Run Macro In Another Workbook Using VBA
Protect a Closed Workbook
Protecting a closed workbook through VBA is more involving. Typically, you need to have the workbook active to apply protection.
However, you can utilize a workaround. You can generate a VBA code in an open workbook to open a closed workbook, protect it, and then close it.
Here’s an example code that opens the ‘Census Report.xlsm’ workbook, protects it without a password, saves the changes, and closes it:
Sub ProtectClosedWorkbookWithoutPassword()
' Declare a Workbook variable
Dim wb As Workbook
' Specify the file path of the workbook
Dim filePath As String
filePath = "C:\Excel Tutorials\Census Report.xlsm"
' Open the workbook located at the specified file path
Set wb = Workbooks.Open(filePath)
' Protect the workbook
wb.Protect
' Save the changes made to the workbook
wb.Save
' Close the workbook
wb.Close
End Sub
This macro opens the workbook located at “C:\Excel Tutorials\Census Report.xlsm”, applies protection to its structure, saves the workbook to preserve the changes, and then closes it.
Also read: VBA Save Workbook
Protect All the Closed Workbooks in a Folder
To protect all closed workbooks in a folder, we will first have to open each workbook using the VBA code, protect them and then close them.
Here is a VBA code that protects all the workbooks in the specified folder.
Sub ProtectWorkbooksInFolder()
' Disable screen updating for performance improvement
Application.ScreenUpdating = False
' Declare variables for folder path and file name
Dim folderPath As String
Dim fileName As String
' Declare a Workbook variable
Dim wb As Workbook
' Specify the folder path
folderPath = "C:\Reports\"
' Get the first Excel file in the folder
fileName = Dir(folderPath & "*.xls*")
' Loop through all Excel files in the folder
Do While fileName <> ""
' Open the workbook
Set wb = Workbooks.Open(folderPath & fileName)
' Protect the structure of the workbook without a password
wb.Protect Structure:=True
' Save the changes
wb.Save
' Close the workbook
wb.Close
' Get the next file name
fileName = Dir
Loop
' Re-enable screen updating
Application.ScreenUpdating = True
End Sub
This code uses the ‘Do While Loop’ construct to iterate through all workbooks in the ‘Reports’ folder, open each one, protect it without a password, save it, and close it.
Also read: VBA Activate Workbook
Protect All Open Workbooks That Have a Specific Word in Name
You can adapt the following code to your requirements to protect all open workbooks with a specific word in the file name without a password:
Sub ProtectWorkbooksWithSpecificWordInName()
' Declare a Workbook variable for iteration
Dim wb As Workbook
' Declare a String variable to store the keyword
Dim keyword As String
' Set the keyword to search in workbook names
keyword = "Report"
' Loop through each open workbook
For Each wb In Workbooks
' Check if the workbook's name contains the keyword
If InStr(1, wb.Name, keyword, vbTextCompare) > 0 Then
' Protect the workbook if it contains the keyword
wb.Protect
End If
Next wb
End Sub
This code utilizes the ‘InStr’ function to check if a specific word (‘Report’ in this example) is in the workbook’s name. If the keyword is found (i.e., the result of ‘InStr’ is greater than 0), the code inside the If block is executed, and the workbook is protected without a password.
In case you want to protect these files with a password, you can use the below code:
Sub ProtectWorkbooksWithSpecificWordInName()
' Declare a Workbook variable for iteration
Dim wb As Workbook
' Declare a String variable to store the keyword
Dim keyword As String
' Set the keyword to search in workbook names
keyword = "Report"
' Loop through each open workbook
For Each wb In Workbooks
' Check if the workbook's name contains the keyword
If InStr(1, wb.Name, keyword, vbTextCompare) > 0 Then
' Protect the workbook if it contains the keyword
wb.Protect Password:="ThePassword"
End If
Next wb
End SubHow to Tell If a Workbook is Protected
You can tell a workbook is protected by looking at the following:
- The ‘Protect Workbook’ icon on the ‘Protect’ group of the ‘Review’ tab is highlighted as appears below.
- When you right-click on a sheet tab, the shortcut menu shows options ‘Insert,’ ‘Delete,’ ‘Rename,’ ‘Move or Copy,’ and ‘Hide,’ among others, are unavailable.
- When you click ‘File’ on the Ribbon and then select ‘Info’ on the Backstage screen, you will see the information indicating that the workbook’s structure has been locked beside the ‘Protect Workbook’ drop-down button.
Also read: VBA Open Excel Workbook
Benefits of Protecting a Workbook
Protecting a workbook’s structure in Excel can have the following benefits:
- Prevent Unwanted Changes: Protecting the workbook structure stops accidental changes, like deleting or moving sheets. This is really useful when many people are working on the same workbook.
- Keep Data Accurate: By stopping the addition, removal, or moving of sheets, your data and formulas stay organized and correct. This keeps your data reliable.
- Safeguard Your Template: If you’ve made a workbook template, protecting it ensures that others won’t unintentionally alter its layout. This keeps the template consistent and as you intended.
- Preserve Formulas and Links: By protecting the structure, you stop people from renaming, rearranging, or deleting sheets, which could mess up formulas and links in your workbook.
- Enhance Security and Privacy: Using a password to protect your workbook helps keep out unauthorized people, especially important for information in hidden sheets that’s sensitive or private.
- Ensure Consistent Layout: For workbooks that need a certain layout, protecting it makes sure everyone follows the same design. This is really helpful in business settings for consistent reporting and data presentation.
Note: Protecting a workbook’s structure prevents users from making structural changes like adding, deleting, or moving sheets. However, they can still edit the content within the cells or change the data itself.
If you want to restrict changes to the content, use other protection features like worksheet protection with a password or cell locking or encrypting the file to require a password to open the workbook.
In this article, I covered various scenarios where you can use VBA to protect an Excel workbook. I hope you found the article helpful.
Other Excel articles you may also like:
