locked
Modify Commands in Existing Excel Shortcut Menues (Excel 2010) RRS feed

  • Question

  • I have a COM add-in in Excel 2010 and need to modify the "Move or Copy" command.  I want to intercept the Click event and decide whether to run some custom code or let it run the built-in code.  I had this working in Excel 2003 and 2007.

    First I created a commandbarbutton

    Private WithEvents cstmSheetCopy As Office.CommandBarButton

    During Sartup of the add-in I have it reference the "Move or Copy" command in the shortcut menues that's displayed when you right-click on a sheet tab:

    cstmSheetCopy = ThisApplication.CommandBars("Ply").Controls("Move or Copy...")

    Then I aded the Click event (I replace all work code a Msgbox for testing):

    Private Sub cstmSheetCopy_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles cstmSheetCopy.Click
        MsgBox("Here")
    End Sub

    If a person right-clicked on a sheet tab and selected "Move or Copy", it would run the cstmSheetCopy_Click code in both Excel 2003 and Excel 2007.  In Excel 2010 I can't even getthe Msgbox to show.

    How do I get this working in Excel 2010?

    Thanks

    Tuesday, October 19, 2010 1:45 PM

Answers

  • Hi John

    <<After several hours, all I can find are methods to add additional IRibbonControls to existing menus.  I still can't find anything on catching an existing control's click event.>>

    No, you can't do that directly. When working with the Ribbon you have to re-think your approach. As I wrote, you'd need to repurpose the command, or replace it in the context menu with your own. There are no "click events".

    Repurpose: put a <commands> section in your Ribbon XML. You an assign a callback to the onAction attribute of the command, so that your code executes instead (no matter how the command is called in the application). You can choose to disable it from the start, or disable it dynamically (getEnabled).

    Or you can write RibbonXML for that context menu, use the idMso of this button control to define the button, and make it invisible or disable it. You can then define your own button that performs the code you require.


    Cindy Meister, VSTO/Word MVP
    Wednesday, October 20, 2010 5:48 PM
  • Nevermind the previous post.  I used the Repurpose approach.

    The statement in my Ribbon1.XML

    <?xml version="1.0" encoding="UTF-8"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
      <commands>
        <command idMso="SheetMoveOrCopy" onAction="CustomMoveOrCopy" />
      </commands>
    </customUI>

    The CustomMoveOrCopy sub I added to the Ribbon1.vb

    Public Sub CustomMoveOrCopy(ByVal control As Office.IRibbonControl, ByRef cancelDefault As Boolean)
      Dim XLapp As Excel.Application
      Dim UseCustomAction As Boolean
          XLapp = Globals.ThisAddIn.Application
          UseCustomAction = UseCustomSheetMoveOrCopy(XLapp)
          cancelDefault = UseCustomAction
    End Sub

    The function UseCustomSheetMoveOrCopy checks the active sheet for certain conditions to see if it should use my code to move or copy the sheet.  If it uses my code, it returns TRUE and the default command is cancelled.  If the conditions are not met, it returns FALSE and the control's default command is executed.  The UseCustomSheetMoveOrCopy function needed the Excel Application object which I aquired using XLapp = Globals.ThisAddIn.Application

     Thanks for your guidance

    John

     

     

    Friday, October 22, 2010 9:55 PM

All replies

  • Hi John

    As I'm sure you're aware, Office started transitioning away from CommandBars and to Ribbon XML in Office 2007. This was extended in Office 2010 to include the context menus. Commands in the context menus are now (mostly) controlled via the "Office fluent UI".

    You will need a Ribbon in your Add-in project. You can choose whether to repurpose the Copy command, or to modify the context menu.

    You'll find information on how to address the context menu (its ControlID) in the following links

    http://www.microsoft.com/downloads/en/details.aspx?familyid=3F2FE784-610E-4BF1-8143-41E481993AC6&displaylang=en
    http://code.msdn.microsoft.com/contextmenusaddin

    This page has general information on using the Office Fluent UI in Office 2010

    http://msdn.microsoft.com/en-us/office/ff802688.aspx


    Cindy Meister, VSTO/Word MVP
    Tuesday, October 19, 2010 4:17 PM
  • Thank you for your response.

    After several hours, all I can find are methods to add additional IRibbonControls to existing menus.  I still can't find anything on catching an existing control's click event.

    I changed to using:

    cstmUnitSheetCopy = ThisApplication.CommandBars.FindControl(, 848)

    where 848 is the ID for the control "SheetMoveORCopy" (from the ExcelControls.xlxs installed by Office2010ControlIDs.exe)

    cstmUnitSheetCopy.Caption shows the correct caption SheetMoveOrCopy, cstmUnitSheetCopy.Excecute brings up the Move or Copy dialog so I know the Commandbarbutton is being correctly referenced (or so it seems).  It also seems to be the same feedback as I was previously getting.

    I still can't get the cstmUnitSheetCopy.click event to execute.

    I tried pointing the OnAction to a sub within the class.  That threw an error saying it couldn't find the "macro"

    I'm using VSTO and Visual Studio 2010.

    I appreciate all the documentation you've pointed me to.  It will be very helpfull when I get to putting all my menues into their own tab and off the Add-Ins tab.

    Thanks

     

    Wednesday, October 20, 2010 5:18 PM
  • Hi John

    <<After several hours, all I can find are methods to add additional IRibbonControls to existing menus.  I still can't find anything on catching an existing control's click event.>>

    No, you can't do that directly. When working with the Ribbon you have to re-think your approach. As I wrote, you'd need to repurpose the command, or replace it in the context menu with your own. There are no "click events".

    Repurpose: put a <commands> section in your Ribbon XML. You an assign a callback to the onAction attribute of the command, so that your code executes instead (no matter how the command is called in the application). You can choose to disable it from the start, or disable it dynamically (getEnabled).

    Or you can write RibbonXML for that context menu, use the idMso of this button control to define the button, and make it invisible or disable it. You can then define your own button that performs the code you require.


    Cindy Meister, VSTO/Word MVP
    Wednesday, October 20, 2010 5:48 PM
  • OK, now things are a little clearer.

    It seems making the original Move Or Copy invisible, then replace it with my own is the better approached.  I originally considered it but adding cstmUnitSheetCopy.Visible = False had no effect.  I guess the big part I'm missing is that I MUST get a Ribbin XML into my project to access existing menus and controls.  So here's the plan:

    1) Make the Original invisible.

    2) Put in my own

    3) When it's clicked it will check for conditions to see if I want my code to run or the original.

    4) If I want the original to run, just call the original's .Execute

    I'll also see what I can find on Repurpose

    Thanks

    Wednesday, October 20, 2010 6:24 PM
  • OK, now things are a little clearer.

    It seems making the original Move Or Copy invisible, then replace it with my own is the better approached.  I originally considered it but adding cstmUnitSheetCopy.Visible = False had no effect.  I guess the big part I'm missing is that I MUST get a Ribbin XML into my project to access existing menus and controls.  So here's the plan:

    1) Make the Original invisible.

    2) Put in my own

    3) When it's clicked it will check for conditions to see if I want my code to run or the original.

    4) If I want the original to run, just call the original's .Execute


    Hi John: Yes, that's pretty much the way it could go.

    RE "The original's .Execute". Actually, that would now be CommandBars.ExecuteMSO "idMsoOfTheRibbonCommand" rather than calling a specific commandbar control. Same idea, different syntax :-)


    Cindy Meister, VSTO/Word MVP
    Wednesday, October 20, 2010 6:55 PM
  • Cindy,

    Thanks for all your help.  I've made a lot of progress but bumped into a wall.

    I've added a Ribbon XML to my project in VB.NET and editted it.

    <?xml version="1.0" encoding="UTF-8"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
      <ribbon>
        <tabs>
          <tab idMso="TabHome">
            <group idMso ="GroupCells">
              <menu idMso="FormatCellsMenu">
                <button idMso="SheetMoveOrCopy" visible="false"/>
                <button id="cstmMoveOrCopyInTab" label="xMove Or Copy" onAction="CustomMoveOrCopy"/>
              </menu>
            </group>
                  
          </tab>
        </tabs>
      </ribbon>
      <contextMenus>
        <contextMenu idMso="ContextMenuWorkbookPly">
          <button idMso="SheetMoveOrCopy" visible="false"/>
          <button id="cstmMoveOrCopy" label="xMove Or Copy" onAction="CustomMoveOrCopy"/>
        </contextMenu>  
      </contextMenus>
    </customUI>

    The <contectMenus> portion works great. The original Move or Copy button is gone. My replacement button, "xMove or Copy" is there and I have it working (I'll remove the "x" and move it into the original's location when I'm finished testing).

    However, the <Tabs> portion is not working.  The only thing I can do with the "GroupCells" group is hide GroupCells.  I can't add a button to it. I can't hide FormatCellsMenu, hide the Move or Copy, or add a button to FormatCellsMenu.

    I've been using "CustomizingCentex Menus in Office 2010" and "Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)" as guides and seem to be in line with their examples.  Where I'm I going wrong?

    Thanks

    John

     

    Thursday, October 21, 2010 5:07 PM
  • Nevermind the previous post.  I used the Repurpose approach.

    The statement in my Ribbon1.XML

    <?xml version="1.0" encoding="UTF-8"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
      <commands>
        <command idMso="SheetMoveOrCopy" onAction="CustomMoveOrCopy" />
      </commands>
    </customUI>

    The CustomMoveOrCopy sub I added to the Ribbon1.vb

    Public Sub CustomMoveOrCopy(ByVal control As Office.IRibbonControl, ByRef cancelDefault As Boolean)
      Dim XLapp As Excel.Application
      Dim UseCustomAction As Boolean
          XLapp = Globals.ThisAddIn.Application
          UseCustomAction = UseCustomSheetMoveOrCopy(XLapp)
          cancelDefault = UseCustomAction
    End Sub

    The function UseCustomSheetMoveOrCopy checks the active sheet for certain conditions to see if it should use my code to move or copy the sheet.  If it uses my code, it returns TRUE and the default command is cancelled.  If the conditions are not met, it returns FALSE and the control's default command is executed.  The UseCustomSheetMoveOrCopy function needed the Excel Application object which I aquired using XLapp = Globals.ThisAddIn.Application

     Thanks for your guidance

    John

     

     

    Friday, October 22, 2010 9:55 PM