Forum Discussion
Solved
Lock options on a slicer
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 cen...
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.
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.
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.