Having trouble modifying an Add-In ribbon interface RRS feed

  • Question

  • I have what I hope is a simple question - I support an Excel add-in that's been in-use continuously for a while (over 10 years). I've made tweaks here and there to ensure it still functions, but now have hit a wall while trying to add new buttons to the interface to run some of the new macros I've set up for it. 

    The code looked straightforward when I started, but I've found that I can neither add a new button nor remove any of the existing ones - whether I comment out the code for an existing button, or add a new tab using the same code, it has no affect on what appears on the interface.

    Here is one sample of the menu code in the xlam:

    Set mnuAPPItem=mnuAPP.Controls.Add(Type=msoControlButton)
    with mnuAPPItem
      .Caption()="Lock Data Worksheets"
    End With

    'sMacroPrepend' is the filename for the particular workbook containing the macros, and 'LockData' is one of the macros I wanted to activate through the menu. All this particular macro does is lock a few specific tabs to prevent editing. 

    The actual macro is irrelevant (this one just locks down certain tabs to prevent anyone from editing them)- but I can't figure out why nothing I do can change the ribbon interface, and am wondering what I'm missing. I am working in Win10/Office 365. 

    • Edited by GHJ001 Friday, May 3, 2019 8:53 PM
    Friday, May 3, 2019 8:52 PM

All replies

  • GHJ,
    re:  menus in Excel

    XL 2010 was the last Excel version where custom menus worked.
    Even then the existing Ply menu items cannot not be altered (you can add new items).
    XL2013 + changed to the Single Document Interface (SDI) which complicated things further.
    (a separate window for each workbook)

    You are effectively screwed, unless you learn XML (required for the Ribbon) or can find some Ribbon code to copy.
    You can add buttons to the worksheet, if that helps, or have users enter Sub names in the "Macros" item on the Ribbons View tab.

    I would love to be proven wrong.

    Custom_Functions add-in (19 new functions)
    Download from MediaFire

    Saturday, May 4, 2019 2:04 PM
  • Gracias - that does help quite a bit. I opened up the addin with an XML interface editor, and I think I know how to proceed. It looks like the ribbon is also referencing a schema from 2006, so I've probably got my work ahead of me in updating it, but now I know where to start.
    Monday, May 6, 2019 3:54 PM
  • As already mentioned, except for popups, commandbars were replaced by the Ribbon in 2007. However they still exist and shown in a different way on the Add-Ins tab for legacy purposes. Just enable the Add-Ins tab via Options / Customize. You might also want to enable the Developer tab at the same time.
    Wednesday, May 8, 2019 7:48 AM