Anchoring Pivot Chart and Slicer without locking entire worksheet

[curtishavak]

I am trying to create a "dashboard" style report using a pivot table and pivot chart with slicers. All of these objects (table, chart and slicers) are in the same worksheet. However, the users of the report (read executive management) are not very Excel savvy and I want to protect the format of the report. So, I want to protect the sheet and only allow access to the Pivot table options. However, in order to make the slicers usable, it appears that I have to allow them to edit objects. This unfortunately means that they can then move those objects (the Slicers and Chart).

I am looking for a way to lock the slicers and pivot chart in position. I have already accounted for column width changes created by modification of the pivot table parameters by selecting the "Don't move or size with cells" option under PivotChart Tools > Size > Format Chart Area > Properties > Object positioning. This is the default/locked-out setting for the slicers.

Please help! Thanks.

 

[curtishavak]

bump

 

[curtishavak]

bump

 

[curtishavak]

Help me Mr. Excel forum. You're my only hope!

 

[xsalad_bar]

I'm having the same issue in Excel 2010. I want users to be able to use the Slicers to filter data, but I don't want them to be able to drag the slicers out of position.

When I set the slicer setting to locked, it also removes the user's ability to click/select slicer items. I checked "Use Pivot Table Reports" in the protect menu, but that didn't solve the problem.

 

[randersonTM]

I'm having the same issue as well. Y'all have any luck on this?

 

[xsalad_bar]

Nope. This issue (and many other issues) bugged me all summer.

I just had to leave the slicers unlocked and hoped that nobody would mess them up or move them too badly out of place.


The only thing I could possibly imagine as a workaround for this would be to create a bunch of your own control buttons. Each button would match with one of the categories in your slicer. When the user clicks your button, you would use Visual Basic code to manually select and activate that category in the slicer. It would look something like:


if ActiveWorkbook.SlicerCaches("YOUR SLICER NAME").SlicerItems("THE CATEGORY IN THE SLICER").Selected = True Then
ActiveWorkbook.SlicerCaches("YOUR SLICER NAME").SlicerItems("THE CATEGORY IN THE SLICER").Selected = False

else ActiveWorkbook.SlicerCaches("YOUR SLICER NAME").SlicerItems("THE CATEGORY IN THE SLICER").Selected = True
end if

You would run this code whenever the button created for that category is clicked. It would toggle the setting of that individual category on and off with each click.

This is an ugly way of doing things, and would make interacting with slicers a lot slower because you wouldn't be able to click and drag to select categories. It's probably not worth the effort.

 

[randersonTM]

If that's the case then it's probably easier to create multi-select list boxes on the page that populate with all of the available values in that pivot fields you want to filter and then create a refresh button that loops through and filters the pivot table based on the selected values. I developed this for another project and if you or anyone else is interested I can dig it up and post it.

 

[Dov Rine]

Solved for (Excel 2010): Anchoring Pivot Chart and Slicer without locking entire worksheet

I know that this is a very old thread, but it came up near the top of the search results, so I'm posting a solution here that I found at:

https://www.sageintelligence.com/ex...he-pivottable-with-full-slicer-functionality/

Note: I have only tried and verified this in Excel 2010.

The steps to protect the sheet without preventing use of the slicers are:

1. Right click on the slicer
2. Select Size and Properties
3. Select Properties
4. Uncheck Locked
5. Close the Size and Properties dialog
6. Repeat steps 1 - 5 for each Slicer on the sheet
7. Protect the Worksheet (Note: There is more than 1 way to get to these options.)
   1. Right click on the tab showing the name of the worksheet
   2. Select Protect Sheet
   3. Uncheck all checkboxes except Select unlocked cells and Use PivotTable reports
   4. Enter a password if necessary. You do not need to enter a password for this to work. The password is only necessary if you don't want users to be able to unprotect the sheet.
   5. Click OK to close the dialog
8. Verify that the elements of the sheet cannot be moved or modified and that the sliders work as intended.

 

[Chry5alid_188]

Re: Solved for (Excel 2010): Anchoring Pivot Chart and Slicer without locking entire worksheet

I know that this is a very old thread, but it came up near the top of the search results, so I'm posting a solution here that I found at:

https://www.sageintelligence.com/ex...he-pivottable-with-full-slicer-functionality/

Note: I have only tried and verified this in Excel 2010.

The steps to protect the sheet without preventing use of the slicers are:

1. Right click on the slicer
2. Select Size and Properties
3. Select Properties
4. Uncheck Locked
5. Close the Size and Properties dialog
6. Repeat steps 1 - 5 for each Slicer on the sheet
7. Protect the Worksheet (Note: There is more than 1 way to get to these options.)
   1. Right click on the tab showing the name of the worksheet
   2. Select Protect Sheet
   3. Uncheck all checkboxes except Select unlocked cells and Use PivotTable reports
   4. Enter a password if necessary. You do not need to enter a password for this to work. The password is only necessary if you don't want users to be able to unprotect the sheet.
   5. Click OK to close the dialog
8. Verify that the elements of the sheet cannot be moved or modified and that the sliders work as intended.

This doesn't work either... This is SOLVED however...

Confirmed in both Excel 2013 and Excel 2016.

SOLUTION:

1. Right Click on Slicer and choose "Size and Properties"
2. Under "Position and Layout" check "Disable resizing and moving"
3. Under "Properties" choose relevant setting...

Locking/Protecting the worksheet is not necessary. The slicer now will no longer easily resize unless the user right clicks on it and then revisits above steps.

Protecting the sheet while the slicer is locked doesn't allow the user to use the slicer. Even if you enable "Use PivotTable & PivotChart"