Pivot Chart Dashboard - 1) move cursor to select slicer element 2) protect the physical pivot chart from being selected

[mrpaw]

Hi all,

I have a dashboard of seven pivot charts on a single worksheet. Worksheet is protected to not allow selection of locked cells. All is great and I love the new slicers. Well, almost.

My first problem, when a user selects a slicer the worksheet jumps back to the last cursor location before protecting. I can find nothing even remotely related to this so I'm open to any suggestions that will move the cell reference to the selected slicer location.

Second, and the most worrisome, even though protected from everything except "allow users to: Use PivotTable & PivotChart", users can still select and accidentally delete the pivot chart.

 

[SpillerBD]

What version as some behavior may be version specific.

I'm working in 2013 and making sure the one cell that the Pivot Table is assigned to ( see by using he Move Pivot Table) is protected I was able to keep from being able to delete the Pivot Table. Using only three check-boxes in the security options.

As to the cursor "jumps back" is probably from Excel not having a good cell to go to if the "allow selection of locked cells" has not been selected.

 

[mrpaw]

Thank you SpillerBD. I was able to prevent the chart deletion.

Do you know of any VBA that would move the cursor to the underlying cell behind the slicer? With 7 charts on one sheet I need the cursor to stay where the user is looking when he selects a slicer option. I turned off the locked cells option in the protection. It seems the slicers "float" over the spreadsheet thus have no cell reference.

 

[SpillerBD]

I wouldn't expect the cursor (active cell) to change when selecting a slicer.
Events isn't my forte, but I'm sure that is where to begin.
Jerry Sullivan has proven a master of the Pivot Table VBA. You might see if he already has a post that might cover this.