Protecting Your Excel Workbooks: A Friendly Guide

When it comes to safeguarding your Excel workbooks, you have a wealth of options at your fingertips, especially through coding.n this blog, we’ll explore the different types of protection available, including workbook, worksheet, and shared workbook protections. Whether you’re looking to prevent unauthorized changes or ensure the integrity of your data, we’ll help you understand how to implement these protections effectively. Let’s dive into the various options so you can make the most of Excel’s security features!

• Workbook.Protect: This feature allows you to safeguard the structure of your workbook, preventing users from making unwanted changes to the windows and sheets.
• Workbook.ProtectSharing: This option helps protect the sharing features and tracked changes when your workbook is being used by multiple users.
• Worksheet.Protect: With this, you can lock down specific worksheets, stopping any modifications to them.

Workbook.Protect: What Can It Do?

If you want to keep users from seeing hidden worksheets or from adding, moving, deleting, or renaming worksheets, you can protect your workbook’s structure with a password.

Code (VBA and VSTO):
Workbook.protect(Password, Structure, Windows)

Password	String	optional default “”	specifying a case sensitive password
Structure	Boolean	optional default False	protects the workbook’s structure, preventing users from adding, deleting, rearrange the order or renaming sheets. Cell editing is still allowed
Windows	Boolean	optional default False	the workbook window cannot be resized or moved

Unprotect:
Workbook.Unprotect(Password)

Helpful Tips for Workbook Protection

• Understand Its Limitations: Remember that protecting a workbook isn’t the same as encrypting it. If your data is highly sensitive, consider using encryption instead.
• Be Cautious with Macros: If you’re using macro-enabled workbooks, be careful—users can still run VBA macros unless you restrict access.
• Protect Your VBA Project: To prevent users from altering your code, consider locking your VBA project. Open Visual Basic for Applications (Alt + F11), go to Tools > VBAProject Properties, check Lock project for viewing, and set a password.

Workbook.ProtectSharing: What’s It For?

Protecting a shared workbook means keeping users from changing important settings, like turning off sharing, altering change tracking history, or changing the workbook’s structure.

• Turn Off Sharing: Users won’t be able to disable sharing or stop tracking changes without the password.
• Protect Workbook Structure: Prevents actions like adding, deleting, hiding, or renaming worksheets.
• Change History: Keeps the workbook’s change history intact, so users can’t delete or modify who made what changes.
• Optional Password: Use a password to protect sharing settings, keeping unauthorized users from making changes.

Code:
VBA
Workbook.ProtectSharing(FileName, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, SharingPassword, FileFormat)
VSTO
Workbook.ProtectSharingEx(FileName, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, SharingPassword, FileFormat)

Filename	String	optional default: current workbookname	The name of the saved file. You can include a full path; if you don’t, Microsoft Excel saves the file in the current folder.
Password	String	optional default: “”	specifying a case sensitive password
WriteResPassword	String	optional default :“”	A string indicating the write-reservation password for this file. If a file is saved with the password and the password isn’t supplied when the file is opened, the file is opened read-only
ReadOnlyRecommended	Boolean	optional default: False	If true, Excel displays a message when the file is opened, recommending that the workbook be opened as read-only.
CreateBackup	Boolean	optional default: False	If true, a backup of the workbook will be created.
SharingPassword	String	optional default “”	A password that protects workbook sharing settings.
FileFormat	String	optional default: current workbook format	Specifies the format to save the workbook in (.xlsx, .xls, etc.).

Unprotect:
Workbook.UnprotectSharing(Password)

Worksheet.Protect: What’s Included?

The Worksheet.Protect method allows you to lock down a worksheet and control what users can do while it’s protected.

Code (VBA and VSTO):
Worksheet.Protect(password, drawingObjects, contents, scenarios, userInterfaceOnly, allowFormattingCells, allowFormattingColumns, allowFormattingRows, allowInsertingColumns, allowInsertingRows, allowInsertingHyperlinks, allowDeletingColumns, allowDeletingRows, allowSorting, allowFiltering, allowUsingPivotTables)

Password	String	optional default: “”	specifying a case sensitive password
drawingObjects	String	optional default : True	Protects shapes, charts, and other drawing objects on the worksheet.
contents	Boolean	optional default: True	Protects the contents of cells (this is the most commonly used aspect of worksheet protection). When True, users cannot edit cell values.
scenarios	Boolean	optional default: True	Protects scenarios, which are used for what-if analyses. This prevents users from changing or viewing scenarios.
userInterfaceOnly	Boolean	optional default: False	When set to True, only the user interface is protected, but changes via VBA code are allowed.
allowFormattingCells	Boolean	optional default: False	Allows users to format cells while the worksheet is protected.
allowFormattingColumns	Boolean	optional default: False	Allows users to format columns while the worksheet is protected.
allowFormattingRows	Boolean	optional default: False	Allows users to format rows while the worksheet is protected.
allowInsertingColumns	Boolean	optional default: False	Allows users to insert columns while the worksheet is protected.
allowInsertingRows	Boolean	optional default: False	Allows users to insert rows while the worksheet is protected.
allowInsertingHyperlinks	Boolean	optional default: False	Allows users to insert hyperlinks while the worksheet is protected.
allowDeletingColumns	Boolean	optional default: False	Allows users to delete columns while the worksheet is protected (only those columns that do not contain locked cells).
allowDeletingRows	Boolean	optional default: False	Allows users to delete rows while the worksheet is protected (only those rows that do not contain locked cells).
allowSorting	Boolean	optional default: False	Allows users to sort data while the worksheet is protected.
allowFiltering	Boolean	optional default: False	Allows users to filter data while the worksheet is protected.
allowUsingPivotTables	Boolean	optional default: False	Allows users to interact with pivot tables while the worksheet is protected.

Unprotect:
Workbook.Unprotect(Password)

Allow Selective Editing
If you want to protect most of a sheet while allowing users to edit specific cells, follow these steps:

1. Select the cells you want to leave unprotected.
2. Go to Format Cells > Protection tab and uncheck Locked.
3. Protect the worksheet. Now only the locked cells will be protected, giving you fine control over what users can modify.

A Few Important Password Tips
Don’t Forget Your Password!: If you forget it, you won’t be able to unprotect your workbook or worksheet.
Character Limit: Passwords should ideally be no longer than 15 characters. If you create a password longer than that, Excel will only use the first 15 characters for protection. And when you want to unprotect your file, you only need to enter those first 15 characters.
Create Strong Passwords: Use a mix of uppercase and lowercase letters, numbers, and symbols for stronger protection.

Test Your Protection
After setting up your protections, test the workbook or worksheet under various scenarios to ensure you’ve got the right level of access control. Make sure users can perform necessary actions (like sorting or formatting) without running into unexpected restrictions.