Share via

How to: Programmatically protect worksheets

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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.

Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

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

Note

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

Use the designer

To protect a worksheet in the designer

  1. 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

  1. 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.

Use 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

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

    Globals.Sheet1.Protect(getPasswordFromUser, true);

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

To protect a worksheet by using code in a VSTO Add-in

  1. Call the Protect method of the active worksheet.

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

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

See also