When you protect a chart sheet or worksheet, you can protect or unprotect individual elements of the sheet in the Protect Sheet dialog box (Protection menu, Tools command) by selecting or clearing check boxes for each element.
Protect worksheet and contents of locked cells When selected, prevents users from:
-
Making changes to cells that you did not unlock before protecting the worksheet, unless you've granted a specific user permission to edit to the cells in the Allow Users to Edit Ranges dialog box.
-
Viewing rows or columns that you hid before you protected the worksheet.
-
Viewing the formulas for the cells for which you hid the formulas before you protected the worksheet.
Select locked cells When cleared, prevents users from moving the pointer to cells for which the Locked check box is selected on the Protection tab of the Format Cells dialog box.
Select unlocked cells When cleared, prevents users from moving the pointer to cells for which the Locked check box is cleared on the Protection tab of the Format Cells dialog box. When users are allowed to select unlocked cells, they can press the TAB key to move between the unlocked cells on a protected worksheet.
Format cells When cleared, prevents users from changing any of the options in the Format Cells or Conditional Formatting dialog boxes. If you applied conditional formats before you protected the worksheet, the formatting continues to change when a user enters a value that satisfies a different condition.
Format columns When cleared, prevents users from using any of the commands on the Column submenu of the Format menu, including changing column width or hiding columns.
Format rows When cleared, prevents users from using any of the commands on the Row submenu of the Format menu, including changing row height or hiding rows.
Insert columns When cleared, prevents users from inserting columns.
Insert rows When cleared, prevents users from inserting rows.
Insert hyperlinks When cleared, prevents users from inserting new hyperlinks, even in unlocked cells.
Delete columns When cleared, prevents users from deleting columns. Note that if Delete columns is protected and Insert columns is not also protected, a user can insert columns that he or she cannot delete.
Delete rows When cleared, prevents users from deleting rows. Note that if delete rows is protected and insert rows is not also protected, a user can insert rows that he or she cannot delete.
Sort When cleared, prevents users from using any of the Sort commands on the Data menu, or the Sort buttons on the Standard toolbar. Users can't sort ranges containing locked cells on a protected worksheet, regardless of this setting.
Use AutoFilter When cleared, prevents users from using the drop-down arrows to change the filter on an AutoFiltered range. Users cannot create or remove AutoFiltered ranges on a protected worksheet, regardless of this setting.
Use PivotTable reports When cleared, prevents users from formatting, changing the layout, refreshing, or otherwise modifying PivotTable reports, or creating new reports.
Edit objects When cleared, prevents users from:
-
Making changes to graphic objects
— including maps, embedded charts, shapes, text boxes, and controls — that you did not unlock before you protected the worksheet. For example, if a worksheet has a button that runs a macro, you can click the button to run the macro, but you cannot delete the button. -
Making any changes, such as formatting, to an embedded chart. The chart continues to update when you change its source data.
-
Adding or editing comments.
Edit scenarios When cleared, prevents users from viewing scenarios that you have hidden, making changes to scenarios that you have prevented changes to, and deleting these scenarios. Users can edit the values in the changing cells, if the cells are not protected, and add new scenarios.
Note If you run a macro that includes an operation that's protected on the worksheet, a message appears and the macro stops running.
Contents When selected, users are prevented from making changes to items that are part of the chart, such as data series, axes, and legends. The chart continues to reflect changes made to its source data.
Objects When selected, prevents users from making changes to graphic objects
When you protect a workbook, you can protect or unprotect the structure of the workbook or workbook windows in the Protect Workbook dialog box (Protection menu, Tools command) by selecting or clearing check boxes for each element.
Structure When selected, users are prevented from:
- Viewing worksheets that you have hidden.
- Moving, deleting, hiding, or changing the names of worksheets.
- Inserting new worksheets or chart sheets. Users can add embedded charts to existing worksheets by running the Chart Wizard.
- Moving or copying worksheets to another workbook.
- In PivotTable reports, displaying the source data for a cell in the data area, or displaying page field pages on separate worksheets.
- For scenarios, creating a scenario summary report.
- In the Analysis ToolPak, using the analysis tools that place results on a new worksheet.
- Recording new macros. If you run a macro that includes an operation that can't be performed in a protected workbook, a message appears and the macro stops running.
Windows When selected, prevents users from:
- Changing the size and position of the windows for the workbook when the workbook is opened.
- Moving, resizing, or closing the windows. However, they can hide and unhide windows.
Note In the Visual Basic Editor, programmers can protect macros so that they can't be viewed or changed by users. Programmers can protect macros by using the Protection tab of the Project Properties dialog box in the Visual Basic Editor (Tools menu, Project Properties command). For more information, see Visual Basic Editor Help.