How to: Protect Worksheets

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Application-level projects Microsoft Office version Excel 2003 Excel 2007 For more information, see Features Available by Application and Project Type.

The protection feature in Microsoft Office Excel helps prevent users and code from modifying objects in a worksheet. By default, all cells are locked after you turn on protection.

In document-level customizations, you can protect worksheets by using the Excel designer. You can also protect a worksheet at programmatically at run time.

Note

You cannot add Windows Forms controls to areas of a worksheet that are protected.

Using the Designer

To protect a worksheet in the designer

• Open the Protect Sheet dialog box:

  1. If you are using Excel 2003, on the Excel Tools menu, point to Protection, and then click Protect Sheet.

  2. If you are using Excel 2007, in the Changes group of the Review tab, click Protect Sheet.

  The Protect Sheet dialog box appears. You can set a password and optionally specify certain actions that users are allowed to perform with the worksheet, such as format cells or insert rows.

You can also allow users to edit specific ranges in protected worksheets.

To allow editing in specific ranges

• Open the Allow Users to Edit Ranges dialog box:

  1. If you are using Excel 2003, on the Excel Tools menu, point to Protection, and then click Allow Users to Edit Ranges.

  2. If you are using Excel 2007, in the Changes group of the Review tab, click Allow Users to Edit Ranges.

  The Allow Users to Edit Ranges dialog box appears. You can specify ranges that are unlocked using a password, and users who can edit ranges without a password.

Using Code at Run Time

The following code sets the password (using the variable getPasswordFromUser, which contains a password obtained from the user) and allows only sorting.

To protect a worksheet by using code in a document-level customization

• Call the Protect method of the worksheet. This example assumes that you are working with a worksheet named Sheet1.

  Globals.Sheet1.Protect(getPasswordFromUser, AllowSorting:=True)
  

  Globals.Sheet1.Protect(getPasswordFromUser, 
      missing, missing, missing, missing, missing, missing, missing, missing, 
      missing, missing, missing, missing, true, missing, missing);
  

To protect a worksheet by using code in an application-level add-in

• Call the _WorksheetProtect(Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object) method of the active worksheet.

  CType(Application.ActiveSheet, Excel.Worksheet).Protect(getPasswordFromUser, AllowSorting:=True)
  

  ((Excel.Worksheet)Application.ActiveSheet).Protect(getPasswordFromUser, 
      missing, missing, missing, missing, missing, missing, missing, missing, 
      missing, missing, missing, missing, true, missing, missing);
  

See Also

Tasks

How to: Remove Protection from Worksheets

How to: Protect Workbooks

How to: Hide Worksheets

Concepts

Working with Worksheets

Host Items and Host Controls Overview

Worksheet Host Item

Global Access to Objects in Visual Studio Tools for Office Projects

The Variable missing and Optional Parameters in Office Solutions