Lock Up Your Valuables

If you haven't already, you'll run into many situations that require you to use worksheet protection. When you're developing Excel applications, one of the most common reasons to use worksheet protection is to lock certain sections of the workbook in order to prevent end users from making changes that would break, or in some way negatively affect, the workbook.

Locking up a worksheet presents many challenges. First of all, you need to consider what kind of changes you want to allow end users to make and code appropriately. Thankfully, the Protect method has a number of optional parameters that you can use to allow the end user to make certain kinds of changes. Second, any time you need to make changes to the worksheet programmatically, you must unlock the worksheet, make your changes, and then lock it again. Finally, because you'll need to store the password to the worksheets in your code or elsewhere, you'll then need to lock your project so that the password can't be retrieved simply by looking at the code.

You can use the functions presented in Listing 7.6 and 7.7 to implement the most draconian locking policy—that is, prohibiting any change to the worksheet except changing unlocked cells.

0 0

Post a comment