When Protecting/locking worksheet / cells / shapes, my slicers become not clickable?

[keithaul]

I have a worksheet that I've created as a Dashboard in Excel 2016. The pivot slicers have shapes around the slicers that make the display more appealing to the end user.

I want the shapes and corresponding code behind the shapes not movable/editable by the end users. This also means I don't want the slicers to be moved either.

This is how I have my sheet set up. I've selected an area around all the shapes and slicers. When I select format cells and select the protection tab, the check box for 'locked' is check. When i go to the 'Review' tab and select protect sheet, below is what is selected:

select locked cells
select unlocked cells

If I do the above, my slicers are no longer clickable, which means I can't select any of the fields in the slicer so I can filter my result displayed on the dashboard.

Since I have a shape surrounding my slicers, how can I prevent the end user from moving the slicers around and still allow them to select the criteria in the slicer to filter their data?

 

[starl]

I don't understand why you are selecting the cells around the shapes and slicers...
Have you looked at the other options in the Protect Sheet dialog? If you scroll down, there are options that allow users to use PivotTable Reports, edit Objects, etc...

To let users use the Slicer on the protected sheet:
1. Right-click on the slicer
2. Select Size & Properties
3. Go to Properties
4. Uncheck Locked
5. Click Close
6. Protect the sheet, selecting Use PivotTable Reports as an option.

 

[keithaul]

Thank you, that seems to work. However there are other issues that come up regarding protection.

1. With the example in the original post, after doing your steps, the user is still able to right click on the slicers and use the options that pop up. This means they can still go into size and properties and go to position and layout and uncheck disable resizing and moving. This will allow the slicer to be moved and resized. How do you disable the right clicking on the slicer?

2. On my datasource sheet where users enter more data into the data table, I have navigation shapes that take the user to other sheets. I want to protect these shapes from the user trying to move them or change colors etc. However when I protect the sheet, this disables them from entering more data into the data table. How do I just protect the shapes, but still allow the user to enter data, delete rows/columns, edit data, etc?

On the Protect Sheet Dialog it has options for inserting, deleting rows/columns, but this doesn't work. If I check these boxes, an error pop up stating its a protected sheet when I want to perform these functions

 

[starl]

The options you want are in the slicer Properties....

1. Right-click on the slicer
2. Choose Size & Properties
3. on the Position & Layout tab, check Disable resizing and moving
4. On the Properties tab, select Don't move or size with cells and UNCHECK Locked
5. Protect the sheet as instructed previously.

As for your data sheet - if it's a real Table (vs a dataset), you'll need code (and buttons) to insert rows in it. Here's an example: Autoexpand Excel Tables on Protected Sheets - Excel First

 

[keithaul]

Thanks again for your response. I thought I would put a short video together to show you what is happening on my end. I'm still not getting the results I want.

I think I'm following your directions right, but if I'm not, you'll be able to tell from the video. Here it is:

https://youtu.be/GOw6db7izwU

Let me know your thoughts

Keith

 

[starl]

ok, so based on your video, everything is working correctly but you are worried that a user will redesign your interface.
Excel does not have additional properties to prevent this. You'll have to use VBA.
With your current design, the easiest setup is to use a Before_RightClick event and prevent the context menu from appearing. Of course, a user can get around that by disabling macros.
If that's not good enough, then the alternative is to totally lock down the sheet and use other events to free up specific objects when the mouse is over the object. That's rather complex. And, of course, a determined user can get around that, too.

 

[keithaul]

Thanks for your input and time on this. I don't know what I'll decide to do.

Again thanks for your expertise.

Keith