Lock options on a slicer

[MrStern]

I am looking to see if there is a way to 'lock' a slicer after a selection is made.

We have management reports that span 20 some divisions, and under those twenty divisions are aprox 300 cost centres.

Each Division has its own Director.  So in essence Once Director looks after a Division with aprox 12 cost centres.

We have a management report that has slicers set up for group where we can click on slicers to filter for all options, e.g. division, cost center, month, etc.

When we distribute to the directors, I am looking for a way to lock the division slicer, so they can not filter this slicer, and as a result can only see their division's results.  

I do not want to create 20 individual reports if possible as there is a lot to this.

Is it possible to lock a slicer?  

If so, how?

E.G. looking at the image, I would like to choose from the Company Code slicer 'CLG' and then lock the slicer and send that version to the corresponding Director.  Then I would select the 'CLI' division, lock it and send it to the corresponding Director, etc.

Thanks!

[#2 · Accepted solution]

@MrStern 

https://www.auditexcel.co.za/blog/excel-slicer-with-password-protection/ 

There's plenty of information on the web. The above link is just one of them. It shows you what do. I'm on Mac running the latest Excel version and it works pretty much the same for me. I trust you can figure out how to do it in your own schedules.

[#3]

One way to do this really well, would be to set up your slicers, leave them locked and protect the worksheet; BUT also set up separate named Sheet Views for each of the various divisions and reports you want to make static.

You can leave your slicers visible, so that as people select the sheet view that is relevant to their division, it will be obvious which slicer settings have been applied.

If Sheet Views seem like they might be tricky for some of your users, you can always create buttons with simple Macros on the worksheet somewhere to toggle between the different sheet views. To do that, simply write the following code in a new module --
Sub {Name}()
ActiveSheet.NamedSheetViews.GetItem("{Your Sheet View Name}").Activate
End Sub
Then create a shaped button with the text of the view name you want to toggle to, then assign the new macro to that button.

This will create a group fixed reports based on various slicer settings, that is protected and easily toggled through.