Listing Protecting Worksheet Assets with the Protect Method

Function ProtectWorksheet(ws As Worksheet, sPassword As String) As Boolean On Error GoTo ErrHandler If Not ws.ProtectContents Then ws.Protect sPassword, True, True, True End If

ProtectWorksheet = True Exit Function

ErrHandler:

ProtectWorksheet = False

End Function

As you can see, you can use Listing 7.6 to lock down a worksheet. After the ProtectWorksheet function is run on a worksheet, any cells that are locked (all cells are locked by default) can't be changed in any manner by an end user or programmatically until the worksheet is unlocked. Protect-Worksheet is implemented here as a function so that when other routines call it, they can check the result to see if the worksheet really was protected.

Before using the Protect method, ProtectWorksheet checks the ProtectContents property of the Worksheet object. ProtectContents is a read-only Boolean value that you can use to see if a worksheet is protected or not. You don't need to protect the worksheet if it is already protected. Besides, if you try to protect a worksheet that is already protected, nothing happens. You won't get a run-time error, but it doesn't change how the sheet is protected. For example, if you protect the worksheet using the password "Test" and then call the Protect method using a different password, when you check the worksheet, it'll still be protected by the "Test" password.

You could use ProtectWorksheet and the UnprotectWorksheet method (shown in Listing 7.7) without a password by supplying an empty string ("") as the sPassword parameter.

Listing 7.7 shows an example of a procedure you can use to unprotect a worksheet. It also presents a simple procedure that demonstrates how to call these functions from another procedure.

0 0

Post a comment