none
Office / Excel 2013 ribbon "drop-down" menu RRS feed

  • Question

  • Hi there!

    I need your help! My CEO is refusing to switch from Excel 2003 to Excel 2013!

    He's an Excel power user and wants to arrange his macro buttons in submenus. I attached a screen shot:
    Comparison of Excel 2003 vs. Excel 2013 submenu options

    Using Office 2003 he created myriads of toolbars and was able to easily create submenus. With Office 2013 I was able to easily create a custom tab and custom groups; however, I couldn't create a drop-down menu, similar to the "Print Area" menu in the screenshot.

    Unfortunately I haven't even been able to find the proper name of this widget!

    Is there any way to solve this without resorting to Visual Studio? Using an OCX as Add-In for example?

    Thank you!!!

    L.

    Friday, August 28, 2015 5:24 PM

All replies

  • Hi L

    I'm going to move your question to the more specialized Excel for Developers forum. Am I correct in my understanding that you want to create a dropdown menu with your own menu commands (and not one with built-in menu commands, as in the screen shot)?

    If that is the case then you will need to learn how to create a Ribbon XML customization and add it to the workbook. This does not require Visual Studio, but it's not as simple as File/Options/Customize Ribbon :-)

    You'll find links to all the relevant information in this forum "pinned" message:
    https://social.msdn.microsoft.com/Forums/office/en-US/ba8c9d0b-7312-4503-8167-f2ef86f17435/information-on-the-office-fluent-ui-ribbon-for-developers?forum=officegeneral

    Start with the three-part article about customizing the ribbon for 2007 (it's all still valid for 2013).


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, August 30, 2015 3:52 PM
  • Hello Cindy!

    Thank you for your support! You understood my question well!

    Maybe it doesn't belong to the Excel for Developers forum, since it's an issue in all Office 2013 applications?

    Thank you for your links, I had already found them, but not yet finished studying them.

    I've tried the third party "RibbonX Visual Designer", which does the XML-Customizations, but that's still very tedious!

    Idea 1:

    Is it possible to develop a "generic" COM Add-In in Visual Studio, which lives in the left "Commands" panel of "Customize Ribbon" and can be added by anyuser to a custom group on the right panel? See the mockup I created (left panel: COM Add-In "My Submenu"; right panel: "My Submenu" added to "MyGroup1", then Macro Buttons 3 to 5 added).

    Idea 2:

    Start a VBA script (my Boss is very familiar with VBA) on starting Excel which might look like the pseudocode below, creating the same structure as shown in the mockup.

    Any other suggestions?

    Thank you very much!!!

    Lucas.

    Mockup for Idea 1:

    Mockup of custom Submenu-Add-In for Office 2013


    Code for Idea 2:

    '   WARNING: This is pseudocode!
    Private Sub Workbook_Open()
    
        Set myTab = Application.Ribbon.AddTab("MYTAB")
        Set myGroup1 = myTab.AddGroup("MyGroup1")
    
        With myGroup1
            Set myButtonMacro1 = .AddButton("Macro1", Icon = imageMso("Smiley"))
            Set myButtonMacro2 = .AddButton("Macro2", Icon = imageMso("Anchor"))
            Set mySubmenu = .AddSubmenu("My Submenu")
        End With
    
        With mySubmenu
            Set myButtonMacro3 = .AddButton("Macro3", Icon = imageMso("Cloud"))
            Set myButtonMacro4 = .AddButton("Macro4", Icon = imageMso("Pi"))
            Set myButtonMacro5 = .AddButton("Macro5", Icon = imageMso("Flags"))
        End With
    
        myTab.Visible = True
    
    End Sub




    Sunday, August 30, 2015 7:36 PM
  • Hi asklucas,

    >> however, I couldn't create a drop-down menu, similar to the "Print Area" menu in the screenshot.
    For creating drop-down menu, you will need to use menu element to specify a drop-menu control.
    # 2.2.28 menu (Menu)
    https://msdn.microsoft.com/en-us/library/dd904921(v=office.12).aspx

    >> Start a VBA script (my Boss is very familiar with VBA) on starting Excel which might look like the pseudocode below, creating the same structure as shown in the mockup.

    I am afraid that we could not use vba code to add custom ribbon. As far as I know, there are two ways to deploy a custom Office Fluent Ribbon:
    1.Modify an Office Open XML Formats file created by one of the Microsoft Office applications that support the Office Fluent UI.
    2.Use an add-in.

    With vba code, you could define the method of the button. The link below shows demo to custom ribbon with xml and vba code.

    # Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)
    https://msdn.microsoft.com/en-us/library/aa338202%28v=office.12%29.aspx?f=255&MSPPError=-2147217396#OfficeCustomizingRibbonUIforDevelopers_Callbacks

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, September 1, 2015 8:28 AM