Granular Control of the Analysis Context Menu – Part 2

This article provides information on hiding specific Analysis commands on the Cell Context menu in Excel. For an overview of this series of articles, please see the first article in the series, here: Granular Control of the Analysis Context Menu – Part 1

In order to hide Analysis commands from the context menu, we need to trap the righ-click event, find the CommandBar control for that command, and then set it’s visible property to false. We don’t need to utilise the Analysis API to hide the context menu items.

In this example, I’m hiding the “Prompts” command, but leaving all other Analysis commands visible. I find the “Prompts” CommandBarControl by using a unique tag of “pio77” (which is assigned by the SBO add-in), because there’s a chance that the context menu might have other controls with the same caption. I’ll provide a full list of tags for all Analysis context menu commands in a later article.

I’m trapping the Worksheet_BeforeRightClick event, but you could also trap this event at the workbook level….

In the VBE, find the worksheet that has your Crosstab, and add this code to the worksheet module…

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

  ‘Define a constant for the Cell context menu

  Const EXCEL_CELL_CONTEXT_MENU As String = “Cell”

  ‘Define a constant for the “Prompts” CommandBarControl tag property

  Const SBO_PROMPTS_COMMAND_TAG As String = “pio77”

  Dim cb As CommandBar

  Dim ct As CommandBarControl

  ‘Get a handle to Excel’s Cell context menu

  Set cb = Application.CommandBars(EXCEL_CELL_CONTEXT_MENU)

  ‘Find the Analysis control by it’s tag property

  Set ct = cb.FindControl(, , SBO_PROMPTS_COMMAND_TAG)

  ‘Check we found the control, and if we did, set it’s visibility to hidden

  If Not ct Is Nothing Then

    ct.Visible = False

  End If

End Sub

Now, right-click on a SAP Crosstab, and you shouldn’t be able to see the “Prompts” command. I’ve tested this code in Excel 2007 with Analysis 1.4, but it should work in Excel 2010 too. If you experience problems or different functionality, please add a comment….