Worksheet And Workbook Protection

Excel provides methods with which you can prevent users from modifying your workbooks, worksheets, and VBA code. When you protect a workbook, you prevent users from adding or deleting worksheets and preventing the user from moving worksheets within the workbook. When you protect a worksheet, you are preventing the user from changing values in cells and making other changes to the worksheet. While workbook protection applies to the entire workbook, worksheet protection operates at the sheet level and so different sheets can have different levels of protection. Both workbook and worksheet protection can be assigned a password (but see the Security Caveat section below).

The Protect Workbook and Protect Worksheet items are in the Changes panel of the Review tab of the Ribbon. When you protect a workbook, you are presented with two protection options. Checking the Structure option prevents users from moving, adding, or deleting worksheets in the workbook. The Windows option prevents the user from creating additional windows for the workbook. By default, Structure is enabled and Windows is disabled. You can assign a password for workbook protection settings.

Worksheet protection is a bit more advanced. By default, all cells are locked, but this does not take effect until you protect the worksheet. When you protect the sheet, all cells are locked except those that you have specifically unlocked. When you protect a worksheet, you get a dialog listing a number of operations that you want to allow the user to perform (such as formatting cells) even when the worksheet is protected. You can provide a password to the worksheet protection.

If you want to lock only a few cells and leave the rest of the worksheet unlocked, select all the cells by clicking the Select All button (the gray square above the row numbers and to the left of the column letters), and then go to the Home tab of the Ribbon, and choose the Format item in the Cells panel on the right side of the Ribbon. Here, uncheck the Lock Cell item. This will unlock all cells. Now, go back and lock those cells you want to lock. When you protect the sheet, those cells will be unlocked and can be changed by the user. If you want to lock all but a few cells, select those cells and then uncheck the Lock Cell item in the Cells item on the Home tab of the Ribbon. Then, lock the sheet.

When you lock cells on a worksheet, that protection applies to VBA operations as well as user actions. If your VBA code attempts to modify a locked cell, you will get a runtime error 1004. One way to work around this is to unprotect the sheet before the relevant VBA runs and then reprotect it after the VBA is finished. This is sort of a messy solution. The better way is to use the UserInterfaceOnly flag when you protect the sheet via VBA. You can specify UserInterfaceOnly only in VBA code. There is no user interface element (command bar, menu, etc) for this option; you must use VBA. When you protect a sheet with UserInterfaceOnly, protection is applied to user actions (basically, keyboard and mouse operations), but VBA is free to modify the worksheet as if there was no protection. The UserInterfaceOnly setting is not saved when you close the workbook, so you need to set it when the workbook is opened. The best place to do this is in the Workbook_Open event procedure. For example,

This code should be placed in the ThisWorkbook code module of the workbook's VBA Project.

In addition to protecting a worksheet, you can hide the formulas on the sheet. Normally, Excel will display a cell's formula in the formula bar when the cell is selected, even if the cell is protected. To hide the formula of a cell, click Format in the Cells panel of the Home tab of the ribbon and check the Format Cells option. In that dialog, choose the Protection tab and check the Hidden item. Now, when you protect the sheet, the formula will not be visible in the formula bar. This protection is weak. The formula can be retrieved with simple VBA code.

You can hide and lock your VBA code. In the VBA editor, go to the Tools menu and choose VBA Project Properties. In that dialog, click the Protection tab and check the Lock project for viewing option and enter a password. Once the workbook is saved and closed, the protection will take effect and the user will not be able to view or edit the VBA code.

It must be said that protection in Excel is very weak, almost to the point that it isn't worth using. There are any number of utilities available on the internet that can break the protection and provide a password to bypass any password protection. I use VBAKey and ExcelKey from Passware.com that break passwords, including those in Excel 2010, in a matter of seconds. These products are about US$40 each, so they are well within reach of someone who wants to get at your formulas and code. Protection is better in 2010 than in earlier verisons, but still weak. Most of the password breaking utilities don't necessarily give you the same password that was used to protect the object, but give you a password that will work. You should act as though workbook, worksheet, and VBA protections are there to prevent honest users from making honest mistakes, not to protect valuable intellectual or proprietary property. If someone wants to see and modify your formulas and code, they can do it with a bit of effort.

Another factor regarding workbook and worksheet security in Excel 2007 and later is that what appears to be a single Excel file is actually a collection of a dozen or so XML files. These files can be extracted with a simple zip program such as WinZip or Secure Zip, and then the content of the XML is readable and writable. These are very complicated XML files and require an expert level of XML. Still, this represents another security hole in Excel 2007 and later.

To get better security and protect your intellectual property, you should create a COM Add-in in VB6 or NET to replace your VBA code, and convert all your functions to code that is stored in either an Automation Add-in or a function library in VBNET. In these cases, you are giving to the user only machine-readable code, while the source code remains safely with you. Another alternative is to compile all your functions in a NET Class Library and call them in a XLL created by Excel DNA. (Excel DNA is a very interesting tool for creating XLLs using a regular NET Class Libraries and should be of interest to any intermediate and advanced level devolopers.)