Protecting Charts in Microsoft Excel

Protecting a Chart Sheet

A chart sheet can be protected manually by selecting Protection from the Tools menu, then choosing Protect Sheet. Any combination of two options may be selected:

• Protect Worksheet for Contents
  This option prevents changes to the chart's formats. Chart elements may be selected, but may not be changed. This option also prevents changes to a chart's data links, either through the series formula or through the Source Data dialog. If there are changes to worksheet data that the chart is linked to, the chart will update. Any objects (text boxes or shapes) on the chart are not protected.
   
• Protect Worksheet for Objects
  This option prevents changes to any objects (text boxes or shapes) on the chart. If the text box is linked to a worksheet cell, and the contents of the cell change, the textbox will update. The formatting and source data of the chart may be changed.

The VBA equivalent of this manual sheet protection is:

ActiveChart.Protect Password:="drowssap", DrawingObjects:=True, Contents:=True

Note: despite being protected, the chart sheet may still be deleted.

Protecting a Chart Embedded in a Worksheet

An embedded chart can be protected manually by selecting Protection from the Tools menu, then choosing Protect Sheet. The Protect Worksheet for Objects option must be selected to protect the chart. This command prevents selection of the chart, and therefore it cannot be changed. If there are changes to worksheet data that the chart is linked to, the chart will update.

Protecting the sheet will only protect the chart if its parent chart object is locked (which is the default for an object). To change the Locked status of the chart, hold the Shift key while clicking on the chart to select the chart object. Selecting the chart object displays white handles (small resizing squares around the chart) rather than black handles. Select Object from the Format menu, click on the Protection tab, and check or uncheck the Locked checkbox.

The VBA equivalent of this manual sheet protection is:

ActiveSheet.Protect Password:="drowssap", DrawingObjects:=True

Selectively Protecting a Chart Using VBA

There are several protection-related properties of a chart which can be adjusted in VBA. These work the same way on chart sheets and on embedded charts.

For these selective protection schemes to work, the sheet containing the chart must be unprotected, or the chart itself must be unlocked if the sheet is protected.

• ActiveChart.ProtectData = True
  This prevents changes to a chart's data links, either through the series formula or through the Source Data dialog. If there are changes to worksheet data that the chart is linked to, the chart will update. This has no effect on objects (text boxes and shapes) in the chart.
   
• ActiveChart.ProtectFormatting = True
  This allows selection of individual chart elements, but prevents changes to the formatting of these elements, including the formatting of objects (text boxes and shapes) in the chart. Double-clicking is disabled, as are most right-click menu options. Changing the chart's data links is allowed. Note: In Excel 2010 (and probably 2007) this does not prevent changing the size or shape of data point markers.
   
• ActiveChart.ProtectSelection = True
  This prevents selection of the chart or any of its elements. If an embedded chart is protected in this way, it cannot be selected (activated), so it must be referenced through its parent worksheet and chart object to unprotect:
  ActiveSheet.ChartObjects(1).Chart.ProtectSelection = False

 

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile