Protect Excel Workbook Using VBA

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:

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

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

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 Sub

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

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

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

In 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 Sub
Also 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 Sub

How to Tell If a Workbook is Protected

You can tell a workbook is protected by looking at the following:

  1. The ‘Protect Workbook’ icon on the ‘Protect’ group of the ‘Review’ tab is highlighted as appears below.
  1. 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.
  1. 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment