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]

Rory, Thanks for that example. The option to toggle a Development mode is a nice technique. It worked perfectly using the CustomUI14 part and VBA code together.

I was less successful in getting the CustomUI14 part to work with constant values.
After replacing all instances of "slGetEnabled" and "tbGetVisible" with "false" in the CustomUI14 part, upon reopening the workbook on loading there was an error dialog: a red "X" 400 Title: Microsoft Visual Basic for Applications.
After clearing that message and right-clicking the slicer, the 400 error returned and repeated 4 times after clicking OK before resuming normal operation. Subsequent right-clicks of the slicer did not generate that error.

I tried boiling that down to just the "Cut" button on the ContextMenuSlicer.

This worked...

CustomUI14 part:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<contextMenus>
   <contextMenu idMso="ContextMenuSlicer" >
       <button idMso="Cut" getVisible="tbGetVisible" />
   </contextMenu>
</contextMenus>
</customUI>

In standard code module in workbook:

Private Const cbDEV_MODE As Boolean = False

Sub tbGetVisible(control As IRibbonControl, ByRef returnedVal)
    returnedVal = cbDEV_MODE
End Sub

This didn't give an error message on opening the workbook, but it gave 1 error msg (Red X 400) on the first right-click of the slicer.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<contextMenus>
   <contextMenu idMso="ContextMenuSlicer" >
       <button idMso="Cut" getVisible="false" />
   </contextMenu>
</contextMenus>
</customUI>

Is there a problem with that syntax?

 

[RoryA]

getVisible has to point to a callback. If you use a constant you use the visible attribute instead.

 

[Jerry Sullivan]

Oh now it makes sense. Thanks Rory.

 

[creedsthoughts]

VBA noob here... I wanted to give a heartfelt thanks to the contributors to this forum as it has been a wonderful, helpful resource!

Was having the same issue as the OP and I appreciate the thoughtful responses from Jerry, especially in regards to providing links to learn more about various techniques.

Ultimately used the solution provided by Rory, which was very cool, however I wanted to disable additional controls in the slicer context menu but didn't know how to find out the control names in order to disable them (I only wanted users to be able to sort slicer items from the slicer context menu).

One of the links Jerry provided suggested downloading an Office 2010 add-in that lists the IDs (idMSOs) of context menu controls.

After extensive searching I wasn't able to find this add-in, but I did find the next best thing - a file called,
"Office 2013 Help Files: Office Fluent Users Interface Control Identifiers" at the following link:

Download Office 2013 Help Files: Office Fluent User Interface Control Identifiers from Official Microsoft Download Center

After downloading it, I opened the ExcelControlNames file and filtered the Group/Context Menu Name column by the ContextMenuSlicer item, and the Control Name column contained the names of the controls I needed.

Modified Rory's sample UI code as follows to disable additional controls:

<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" />
      <command idMso="SlicerRefresh" getEnabled="slGetEnabled" />
      <command idMso="ObjectSizeAndPropertiesDialog" getEnabled="slGetEnabled" />
      <command idMso="MacroAssign" getEnabled="slGetEnabled" />
      <command idMso="ObjectBringToFront" getEnabled="slGetEnabled" />
      <command idMso="ObjectSendToBack" getEnabled="slGetEnabled" />
      <command idMso="PasteGalleryMini" 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>

Hope this helps someone!

Using Excel 2010

 

[vsrihari007]

Hi srm057,

This thread explains how to do that. (Use Abhay Gadiya's suggestion- not my clunky VBA suggestion)
Protecting worksheet but maintaining slicer functionality and usability

This helped!! Thanks

 

[jpmiralrio]

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.

Hi! I just did the hole process and when I try to run the Excel and error appears "Unable to run the macro "tbGetVisible". The macro may not be available in this book or all macros have been disabled". I had already checked that I pasted the code in the workbook and active all the macros in the settings. Can someone help me please?.
An apology for my grammar, my native language is Spanish.

 

[RoryA]

The code should be in a normal module not ThisWorkbook.

 

[jpmiralrio]

The code should be in a normal module not ThisWorkbook

Thanks!

 

[mbr00]

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.

@RoryA thank you for that answer, this was extremely useful. A follow up question I had, how did you know that you had to use "command" for the slicer controls instead of "button"? In the excelcontrols files found here they are all referred as buttons. Is there other documentation or some other way that this can be determined? That information would be great for being able to do other customizations in the future.
Thanks again!

 

[RoryA]

I always use command for disabling built-in commands as it's in the <commands> tag.