Protect Workbook with Excel VBA

Objective:

You want to protect an entire workbook (including the structure of worksheets) using VBA. This means:

• Preventing users from adding, deleting, or renaming sheets.
• Protecting the workbook’s structure but allowing the user to interact with the contents of the worksheets.

Steps and Code:

1. Enable Workbook Protection with a Password: First, we’ll write the VBA code to protect the workbook’s structure. This ensures that users can’t alter the structure of the workbook (i.e., add, delete, or rename sheets).
2. Password Protection: You can add an optional password to protect the workbook. This ensures that only authorized users can unprotect it.
3. Unprotecting the Workbook: You can also write code to unprotect the workbook using the password.

Code Example

Sub ProtectWorkbook()
    ' Declare variables
    Dim password As String
    ' Set the password you want to use (make sure to store this password securely)
    password = "YourSecurePassword"
    ' Protect the workbook with password
    ThisWorkbook.Protect Structure:=True, Windows:=False, Password:=password
    ' Inform the user that the workbook has been protected
    MsgBox "Workbook is now protected.", vbInformation, "Protection Status"
End Sub

Sub UnprotectWorkbook()
    ' Declare variables
    Dim password As String
    ' Set the password you want to use to unprotect the workbook
    password = "YourSecurePassword"
    ' Unprotect the workbook using the password
    On Error Resume Next ' In case the password is incorrect
    ThisWorkbook.Unprotect Password:=password
    On Error GoTo 0 ' Turn back to default error handling   
    ' Check if workbook is unprotected successfully
    If Not ThisWorkbook.ProtectStructure Then
        MsgBox "Workbook is now unprotected.", vbInformation, "Protection Status"
    Else
        MsgBox "Failed to unprotect the workbook. Please check your password.", vbCritical, "Error"
    End If
End Sub

Detailed Explanation:

1. Sub ProtectWorkbook:
   • Declaring a Password: The password variable is set to « YourSecurePassword » in this example. You can replace this with any password you want to use.
   • Protecting the Workbook: The ThisWorkbook.Protect method is used to apply protection to the entire workbook.
     • Structure:=True: This argument ensures the structure of the workbook is protected. Users cannot add, delete, or rename sheets.
     • Windows:=False: This argument ensures that the workbook window itself is not protected, meaning users can still resize or move the window.
     • Password:=password: This specifies the password required to unprotect the workbook.
   • Message Box: A message box pops up informing the user that the workbook is now protected.
2. Sub UnprotectWorkbook:
   • Declaring a Password: The password variable is again set to « YourSecurePassword » to allow unprotection.
   • Unprotecting the Workbook: The ThisWorkbook.Unprotect method is used to remove the protection. The Password:=password argument ensures that only the correct password will unprotect the workbook.
   • Error Handling: The On Error Resume Next statement is used to ignore errors if the password is incorrect. After trying to unprotect, On Error GoTo 0 restores normal error handling. If the workbook is successfully unprotected, a confirmation message is shown.
3. Handling Incorrect Password:
   • After attempting to unprotect, the code checks if the workbook’s structure protection is still active using the ThisWorkbook.ProtectStructure property. If the structure protection is still enabled, it means the password was incorrect.

Notes:

• Password Security:
  • While this method works for basic protection, remember that storing passwords directly in code is not the most secure practice. In production environments, consider using other methods to store passwords securely (like encrypted storage).
  • Advanced users may be able to bypass the protection if they know how to crack or retrieve the password from the code.
• Limitations:
  • The ThisWorkbook.Protect method only protects the workbook structure. It does not protect the contents of the worksheets themselves. If you want to protect individual cells or ranges, you would need to apply worksheet protection separately (using ActiveSheet.Protect for each sheet).
  • This protection is not foolproof and is intended for basic use. It can be bypassed with the right knowledge or tools.

Additional Considerations:

You can extend this code to:

• Protect individual worksheets within the workbook.
• Protect specific ranges of cells (e.g., allowing users to edit only certain ranges).