Granular Control of the Analysis Context Menu - Part 3

[Former Member]

This article provides information on restoring specific Excel 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

By default, Analysis hides standard Excel commands on the context menu, when the current selection is on a SAP Crosstab (with the exception, in certain situations, of the Insert Comment command). Like in article 2 of this series (when we hid a specific Analysis command), to restore a specific Excel command, we need to trap the righ-click event, find the CommandBar control for that command, and then set it's visible property to true. We don't need to utilise the Analysis API to restore the context menu items.

In this example, I'm restoring the "Copy" command, but leaving all other Excel commands at their default visibility. Unlike in article 2 (where finding an Analysis command searches on a unqiue tag property), we find the "Copy" CommandBarControl by using a unique ID of 19 (which is assigned by Excel, and constant across 2007 and 2010), because there's a chance that the context menu might have other controls with the same caption. I'll provide a full list of IDs for all Excel 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 "Copy" CommandBarControl ID property

  Const EXCEL_COPY_COMMAND_ID As Integer = 19

  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 Excel control by it's ID property

  Set ct = cb.FindControl(, EXCEL_COPY_COMMAND_ID)

  'Check we found the control, and if we did, set it's visibility to visible

  If Not ct Is Nothing Then

    ct.Visible = True

  End If

End Sub

Now, right-click on a SAP Crosstab, and you should be able to see the "Copy" command in addition to the Analysis commands. 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....