1

I need to add a group header in the Excel command bar during runtime using C#, so that I can separate my Office.CommandBarButton options from the default Excel options. For example, in Excel 2013, if you go and select a row and RMB (right-mouse-button), the default command bar will be displayed with many options. You will notice that there is a header called "Paste Options:" with the standard paste icon to the left. I want to create a similar header (group) like "Paste Options:" using C#.

Btw, I use the following code sample to successfully add several Office.CommandBarButton options in Excel;

    private void AddMyRowMenu() 
    {
       Office.CommandBars commandBars = null;
       Office.CommandBar commandBarRowMenu = null;
       Office.CommandBarButton commandBarButtonMyOptions1;
       try
       {
           commandBarRowMenu = commandBars["Row"];
           commandBarButtonMyOptions1 = (Office.CommandBarButton)commandBarRowMenu.Controls["My Option 1"];
       }
       catch (ArgumentException)
       {
           commandBarButtonMyOptions1 = (Office.CommandBarButton)commandBarRowMenu.Controls.Add(Office.MsoControlType.msoControlButton, oMissing, oMissing, oMissing, oMissing);
           commandBarButtonMyOptions1.BeginGroup = true;
           commandBarButtonMyOptions1.Caption = "My Option 1";
       }
       commandBarButtonMyOptions1.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(commandBarButtonMyOptions1_Click);
    }

I add 3 Office.CommandBarButton options using the above code and need to separate them from the default Excel RMB options for clarity.

Shanon Fernando
  • 11
  • 2
  • Since Office 2007 the CommandBar object model is deprecated - instead, the Ribbon provides the command interface in newer Office versions. So you need to research the Ribbon. Apparently, you mean the context ("right-click") menus: these can also be changed via Ribbon XML. VSTO supports Ribbon XML. – Cindy Meister Jan 27 '19 at 18:56
  • @CindyMeister: Thanks Cindy. I researched the use of the Ribbon.xml, but its still not possible to implement a “Paste Options:” like context menu with an icon to the left. Besides, my context menu buttons are dynamically added, based on conditions, so I feel its not possible to implement using the Ribbon.xml. Anyway, I had to settle with implementing a simple context menu using VSTO. This was achieved by defining a popup menu with type “Office.MsoControlType.msoControlPopup”. – Shanon Fernando Feb 05 '19 at 06:24
  • You just need to keep in mind that the object model is "deprecated" - at some point it may cease to be supported in a future version of the software. As I recall, the approach no longer works in PowerPoint or Word, for example. – Cindy Meister Feb 05 '19 at 06:26

1 Answers1

0

According to Microsoft, only a limited no of control types are available for us to work with https://learn.microsoft.com/en-us/office/vba/api/office.msocontroltype Following is my sample code used to implement the context menu;

private void AddMyRowMenu2() 
{
   Office.CommandBars commandBars = null;
   Office.CommandBar commandBarRowMenu = null;
   Office.CommandBarPopup commandBarRowPopupMenu = null;
   Office.CommandBarButton commandBarButtonMyOptions1 = null;
   Office.CommandBarButton commandBarButtonMyOptions2 = null;
   try
   {
       commandBars = (Office.CommandBars)Application.GetType().InvokeMember("CommandBars", System.Reflection.BindingFlags.GetProperty, null, Application, new object[] { });
       commandBarRowMenu = commandBars["Row"];
       commandBarRowPopupMenu = (Office.CommandBarPopup)commandBarRowMenu.Controls.Add(Office.MsoControlType.msoControlPopup, oMissing, oMissing, oMissing, oMissing);
       commandBarRowPopupMenu.Caption = "My Popup Menu 1";
       commandBarButtonMyOptions1 = (Office.CommandBarButton) commandBarRowPopupMenu.Controls.Add(Office.MsoControlType.msoControlButton, oMissing, oMissing, oMissing, oMissing);
       commandBarButtonMyOptions1.Caption = "My Button Option 1";
       commandBarButtonMyOptions2 = (Office.CommandBarButton) commandBarRowPopupMenu.Controls.Add(Office.MsoControlType.msoControlButton, oMissing, oMissing, oMissing, oMissing);
       commandBarButtonMyOptions2.Caption = "My Button Option 2";
   }
   catch (ArgumentException ex)
   {
       MessageBox.Show(ex.Message, "Test Menu Items", MessageBoxButtons.OK, MessageBoxIcon.Warning);
   }
   commandBarButtonMyOptions1.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(commandBarButtonMyOptions1_Click);
   commandBarButtonMyOptions2.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(commandBarButtonMyOptions2_Click);
}
Shanon Fernando
  • 11
  • 2