Disable delete for Pivot table slicer

[srm057]

Hello,

I am using Excel 2010 pivot table with Slicers. I have protected the sheet while keeping the Slicer unlocked for functionality.

However if I keep the Slicer unlocked in a proctected sheet any body can delete the slicer or remove it from the sheet.


Is there any way I can prevent/disable delete for Slicer while keeping it functional in a protected sheet.

 

[Jerry Sullivan]

Hi srm057,

This thread explains how to do that. (Use Abhay Gadiya's suggestion- not my clunky VBA suggestion)
http://www.mrexcel.com/forum/excel-...intaining-slicer-functionality-usability.html

 

[srm057]

Aready tried it. It doesn't work. Still removable

 

[Jerry Sullivan]

Hmm... When I follow those steps I'm not able to delete the slicer.

What options do you have checked under "allow users of this worksheet to: " when you Protect the workbook?

What method are you using to delete the slicer? In my worksheet, the Delete button on the Slicer's context menu in the Ribbon is greyed out.

 

[srm057]

I only Checked 'Select unlocked cells' and 'Use pivot table reports' I also unchecked locked from Slicer properties. When I protected the sheet I was still able make changes in slicer pivot table connections, and I was also able to remove Slicer from right click menu. I am using Excel 2010.

 

[Jerry Sullivan]

Hard to know why that is happening without being able to replicate that. If you are able to provide an example file, I'll try to find why that isn't working. You could either upload to a file hosting site like box.com or send me a PM and we'll exchange email addresses.

 

[Jerry Sullivan]

Thanks for sending your file. You are right that the slicer can still be deleted after following Abhay Gadiya's suggestion in the linked thread. It appears that those steps will prevent the deleting the Slicer by selecting it then hitting the Delete key; however they do not prevent deleting of the Slicer through the Right-Click menu (through either "Cut" or "Remove slicername")

The only workarounds I can suggest are:
1. The clunky VBA option I suggested in the linked thread. http://www.mrexcel.com/forum/excel-...icer-functionality-usability.html#post3287398

2. Using VBA to individually disable all those methods of deleting slicer (from right-click menu, ribbon menu, and Ctrl-X keys). That's not something I've done, but I've seen many threads on this forum on the more general topic of disabling Cut, Copy, Paste commands, and forcing users to enable macros in order to be able to access the workbook content.

Sorry I couldn't offer a cleaner solution.

 

[srm057]

can we disable the Right Click menu for the slicer only... I know it can be done for pivot table and others

 

[Jerry Sullivan]

can we disable the Right Click menu for the slicer only... I know it can be done for pivot table and others

I did a bit of WebSearch and I'm pretty sure that can be done. Here's some links to get you started....

Ron de Bruin provides an overview, example codes and a download file on the general topic of modifying the right-click menus.
Customizing Context Menus in Microsoft Excel 2010-2013
Ron points out that the users need to have 2010 or later; however anyone using Slicers will need to have 2010 or later.

Microsoft's overview of the context (right-click) menus for Office Apps (not just Excel).
Customizing Context Menus in Office 2010

Office Custom UI Editor: This is a link in the Microsoft overview. Ron de Bruin recommends this tool as well however the link below is to a more recently updated version of the tool than the one that Ron references.
Office Custom UI Editor available - OpenXML Developer - Blog - OpenXML Developer

A repeated complaint in the comments after the Microsoft overview article is people not having a list of Context Menus that are available.
When installed, this Add-In displays the name of the Context Menu as the last item when a Context Menu is displayed.
ContextMenus Add-In for Office 2010 - Release: ContextMenus Add-In for Office 2010

Using this, I was able to confirm that upon Right-Clicking on a Slicer, the Context Menu that is displayed is named: ContextMenuSlicer
Based on all the above, disabling the Right-Click > Cut for Slicers only seems feasible.

 

[RoryA]

You should be able to use something like this:

CustomUI14 part:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<commands>
      <command idMso="SlicerShare" getEnabled="slGetEnabled" />
      <command idMso="SlicerSettings" getEnabled="slGetEnabled" />
      <command idMso="SlicerConnectionsMenu" getEnabled="slGetEnabled" />
      <command idMso="SlicerDelete" getEnabled="slGetEnabled" />
	</commands>
   <ribbon>
	<contextualTabs>
        	<tabSet idMso="TabSetSlicerTools" getVisible="tbGetVisible" />
    	</contextualTabs>
   </ribbon>

<contextMenus>
<contextMenu idMso="ContextMenuSlicer" >
<button idMso="Cut" getVisible="tbGetVisible" />
<button idMso="Copy" getVisible="tbGetVisible" />
</contextMenu>
</contextMenus>
</customUI>

then in the workbook:

Option Explicit
Private Const cbDEV_MODE As Boolean = False
'Callback for SlicerShare getEnabled
Sub slGetEnabled(control As IRibbonControl, ByRef returnedVal)
    returnedVal = cbDEV_MODE
End Sub

'Callback for TabSetSlicerTools getVisible
Sub tbGetVisible(control As IRibbonControl, ByRef returnedVal)
    returnedVal = cbDEV_MODE
End Sub

If you change the cbDEV_MODE constant to true then save and reopen the file you can re-enable the commands for when you are making changes to the file. If you don't need to do that, you can simply make set the visible and enabled attributes to "False" in the XML and skip the code.