Write-Protecting Data in Your Workbook

Write Protection, Not Encryption

There may be times when you want to lock your Excel worksheet so that end users cannot edit or manipulate the data in a worksheet. The ability to present your spreadsheet to end users in a visible, but uneditable format can be very useful for presentation purposes. You can implement worksheet protection using ExcelWriter by setting the WriteProtect property of the Worksheet object.

It is important to note that worksheet protection is meant to lock data for the purpose of presentation only. Worksheet protection should not be confused with encryption, and it should not be used to hide or protect sensitive data. When worksheet protection is activated, users can see the worksheet and all the data within it, but will be prevented from altering the content or formatting the cells. Furthermore, any workbook with worksheet protection can be opened and modified using ExcelWriter.

• How to Activate Worksheet Protection with ExcelWriter
• Removing Worksheet Protection
• Protecting All Worksheets in a Workbook
• Protecting Specific Cells
• Example: Generating a Workbook with Worksheet Protection

How to Activate Worksheet Protection with ExcelWriter

Using ExcelWriter, there are two ways to write-protect worksheets in your workbook:

1. Protect in Microsoft Excel, and Open in ExcelWriter
2. Protect in an ExcelWriter Script

• Protect in Microsoft Excel, and Open in ExcelWriter
  Create a workbook in Microsoft Excel, open the Tools menu, and select Protection -> Protect sheet.... Then, open the spreadsheet in an ExcelWriter script, using either ExcelTemplate or ExcelApplication.Open. ExcelWriter will preserve all existing spreadsheet settings, including worksheet protection.
  
  
  
  
• Protect in an ExcelWriter Script Use the property Worksheet.WriteProtect to active worksheet protection in the ExcelWriter script, as follows:
  
  
  1. Optional: Determine your passcode. If you do not want to set a password to disable write protection, continue to step 2.
     
     In ExcelWriter, the passcode is an integer value that corresponds to a clear-text password string. Excel's password hashing algorithm is private, as such it is not possible to set Worksheet.WriteProtect to a regular string. For example, setting WriteProtect to -1843 sets the worksheet password to "mypassword".
     
     To determine which passcode correponds to a given password string, create a blank workbook in Excel and supply your desired password in the Worksheet Protection creation process. Then, open the workbook using ExcelApplication.Open and read the value of WriteProtect. For example, if your workbook is called "password.xls", use this code to determine the passcode that correponds to the password string you've set for the first worksheet:

     <%
     Set xlw = Server.CreateObject("SoftArtisans.ExcelWriter")
     xlw.Open "password.xls"
     Response.Write(xlw.Worksheets(1).WriteProtect)
     %>

  2. Set WriteProtect to your passcode, for example:

     Worksheet.WriteProtect = -1843

     OR, to enable write protection without setting a password:

     Worksheet.WriteProtect = True

Removing Worksheet Protection

To remove worksheet protection in Microsoft Excel,

1. From the Tools menu, select Protection -> Unprotect Sheet...
2. If a password was set, enter it and click Ok.

To remove worksheet protection in an ExcelWriter script, set Worksheet.WriteProtect to False:

	Worksheet.WriteProtect = False

Protecting All Worksheets in a Workbook

ExcelWriter applies write protection on a per-worksheet basis. If you want to apply protection to all of the worksheets in your workbook, you will need to set it for each worksheet individually. You can use a simple loop like this:

	For Each worksheet in xlw.Worksheets
		worksheet.WriteProtect = passcode
	Next

Protecting Specific Cells

When you activate worksheet protection, all of the cells in the worksheet will be locked by default. However, you can unlock cells within a protected worksheet, allowing the user to fill in or modify some fields.

To unlock cells in Microsoft Excel:

1. Select a cell or cells to unlock.
2. From the Format menu select Cells...
3. Select the Protection tab.
4. Uncheck Locked.

With ExcelWriter, you can unlock cells by setting a style's Locked property to False and applying the style to an individual cell. The following example protects all cells except for A1 and B1:

	'--- Turn on worksheet protection
	WorkSheet.WriteProtect = -1843

	'--- Create an "unlocked" style
	Set unlockedStyle = xlw.CreateStyle
	unlockedStyle.Locked = False
		
	'--- Apply the style
	Worksheet.Cells("A1").Style = unlockedStyle
	Worksheet.Cells("B1").Style = unlockedStyle

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.