Excel VBA - Remove slicer right click menu options

Question

I would like to remove options from the menu that pops up when you right click a slicer.

More specifically, I would like to remove "Remove", "Cut", "Refresh", the two sort options and "Report Connections".

I have tried to follow a similar approach to :

Application.CommandBars("Cell").Controls("Cut").Delete

To find the proper names, I have looped over all commandbars and their controls to find anything about "Slicer" and all they are all under commandbar "Nil".

The control don't have a name property, they have a caption but it looks like you have to remove the "&" otherwise it throws an error.

I have started with the "Remove" option, tried with the caption name (removing the "&") and also tried with the index number, neither of them throw and error, but they don't remove the option from the menu.

Application.CommandBars("Nil").Controls("Remove Slicers").Delete Application.CommandBars("Nil").Controls(198).Delete

I have tried with other Remove controls, index 17, 18, 198, 200 but none of them changed anything.

Is there something else I am missing?

Thanks in advance

Answer 1

I was able to solve this myself. Doing some research, I achieved this using customUI. I have also added some extra customization to remove pivottable controls and tabs, but have marked functionality with comments so can be personalized as needed:

    <customUI xmlns=http://schemas.microsoft.com/office/2009/07/customui>
       <commands>
          <--! Disable Slicer right-click menu controls-->          
          <command idMso="SlicerSettings"  enabled="false" />
          <command idMso="SlicerConnectionsMenu"  enabled="false" />
          <command idMso="SlicerDelete"  enabled="false" />
          <command idMso="SlicerRefresh"  enabled="false" />
          <command idMso="ObjectSizeAndPropertiesDialog"  enabled="false" />
          <command idMso="MacroAssign"  enabled="false" />
          <command idMso="ObjectBringToFront"  enabled="false" />
          <command idMso="ObjectSendToBack"  enabled="false" />
          <command idMso="PasteGalleryMini"  enabled="false" />
          <command idMso="SlicerMultiSelect"  enabled="false" />
          <command idMso="SlicerClearFilter"  enabled="false" />
          <command idMso="SlicerDataSourceOrder"  enabled="false" />
          <command idMso="SlicerSortUp"  enabled="false" />
          <command idMso="SlicerSortDown"  enabled="false" />
       </commands>
       <ribbon>
          <contextualTabs>
             <tabSet idMso="TabSetSlicerTools" visible="false" />
             <--! Hide PivotTable Analyze and Design tabs -->
             <tabSet idMso="TabSetPivotTableTools">
                <tab idQ="TabPivotTableToolsOptions" visible="false" />
                <tab idQ="TabPivotTableToolsDesign" visible="false" />
             </tabSet>
          </contextualTabs>
       </ribbon>
       <contextMenus>
          <--! Hide Slicer right-click menu controls--> 
          <contextMenu idMso="ContextMenuSlicer">
             <button idMso="Cut" visible="false" />
             <button idMso="Copy" visible="false" />
          </contextMenu>
       </contextMenus>
    </customUI>

Extremely helpful resources I used:

Source of most of the code: https://www.mrexcel.com/board/threads/disable-delete-for-pivot-table-slicer.767331/page-2#posts also mentions how to make the disables dynamic if needed, using VBA instead of having to edit the customUI14.xml every time.

How to insert this customUI14.xml file in the workbook (no new tools needed, I used 7-Zip): https://bettersolutions.com/vba/ribbon/document-level-notepad.htm

I'm still not clear why they are called "commands" instead of buttons as they show in the files mentioned below, but it works.

And could get the list of names of different elements from the ExcelControls files found here: https://github.com/OfficeDev/office-fluent-ui-command-identifiers/tree/master

Extra: I was looking for this functionality to make a dashboard-like workbook where users can't delete slicers nor modify pivotTable layouts but still interact with slicers and select cells. This functionality above, combined with protection and disabling right-clicks and pressing the delete key using VBA I believe achieves that. (Disabling the right-click doesn't apply to slicers that's why this extra solution was needed).