Excel – Allow selecting but not editing a chart and slicers in a protected Excel sheet

microsoft excelmicrosoft-excel-2010

I would like to share an excel file that has slicers and charts in it. I want users to be able to use the slicers for filtering and then select and copy the charts. But I want to protect the sheet and prevent any editing of the chart and the slicers.
As far as I can see, the options are to lock the charts and slicers, in which case they are uneditable when the sheet is protected, but then the slicers are frosen and the chart can't be copied (PrintScreen is not an option). OR, to unlock them, in which case they become usable but editable.
Is there a way around this?

Best Answer

You have 2 options. One way is to go for VBA enabled worksheet and add your own logic using VBA, but it requires that your sheet is xlsm and the end user must enable macros in your workbook - but this way you get best control over allowed user's actions. The second way is with xlsx without VBA - but you cannot deny some actions to the user there, like to delete the slicers or change the name of the chart.

1) You can do as recommended here: 

Private Sub Chart1_Click()     
    ActiveSheet.ChartObjects(1).CopyPicture
    MsgBox ("Chart copied to clipboard.  You can now paste this into another application.")        
End Sub

It means you need to convert your workbook to xlsm, workbook with macros. Then add a macro to handle the clipboard action on the chart click.

2) The better way I think would be that you leave the Chart element unlocked on the protected worksheet and modify its properties with a macro:

Sub macro()
ActiveChart.ProtectData = True
ActiveChart.ProtectFormatting = True
ActiveChart.ProtectSelection = False
End Sub

or for all charts on a sheet:

For Each i In ActiveSheet.ChartObjects
    i.Chart.ProtectSelection = False
    i.Chart.ProtectData = True
    i.Chart.ProtectFormatting = True 
Next i

This way you use macros when creating the workbook, but the workbook itself does not contain VBA.

If you create a separate sheet for a chart, you can protect it separately with a password:

Sub macro()
Dim newChart As Chart

Range("C9:C11").Select
Set newChart = ActiveChart

With newChart
.Protect Password:="pwd", DrawingObjects:=True, Contents:=True
.ProtectData = True
.ProtectFormatting = True
.ProtectSelection = False
.ChartObjects(1).Chart.ProtectSelection = False

   'setting other properties
End With

End Sub

As for the slicers, you need to leave them unlocked and to check "Use Pivottable Reports" when locking the worksheet.

Related Question