Wednesday 3 December 2014

Excel 2013 and the Ribbon

I've recently started using Excel 2013, and soon noticed the Ribbon doesn't act the same way as it did with Excel 2007 and Excel 2010. Addins that add menu items to "what used to be" (still is) the Top Menu (Menu Commands on the Add-Ins tab) and toolbars (Custom Toolbars also on the Add-Ins tab) are affected.

After a quick look on the Internet, I found this.

Well now, that changes things a bit...

Actually the recommended method of looping through each Window and deleting controls and toolbars does not always work. As insurance, I recommend you add them as Temporary where applicable. As far as my testing shows, this works without fail. (And it's good practice too).

Anyway, I was talking to my friend Ken Puls (of Excelguru fame) at the time, and he suggested using the Ribbon as it should be - with XML. And Ken should know, being one of the authors of RibbonX - Customizing the Office 2007 Ribbon.

I gave this some thought and decided that's just what I'd do. I've used some very basic XML for my main tools, JMT Excel Utilties back in 2007, which has it own tab, but where was I going to put everything else. Why not in the same place?

Ken kindly provided some XML to add items from different files on the same tab and I went from there. This is where I'm at now. It's been a busy two weeks, not just writing XML, but rewriting a lot of VBA.



If you have already downloaded any of the above addins and use them with Excel 2007 - Excel 2013, you can replace them from the links below. I recommend you do so as I have made some major changes to some of their code. (If any of the menu items or toolbars still show on the Add-Ins tab from the old versions in Excel 2013, you can simply right-click them and delete them)

AET Chart Tools

AET Data Checker

AET Excel Query

AET Find and Replace

AET Status Bar

Calculation Checker

Cell Highlighter (not pictured)

Cell Watch Form

Classic Excel Colors

Marlett Checkboxes

VBA Code Indenter
Also, not shown in the picture, but I have made big improvements to the code. An xlam alternative version has been introduced in case you have problems with the regular VBE version.

Download it here.

JMT Excel Utilities?
Hang on! In the above picture, I didn't see JMT Excel Utilities!

No you didn't. But I can show you this. (Look to the left)



They're making a comeback and will be better than before. They won't be free, but well worth what I'll be asking. Stay tuned.

See you next time.

2 comments :

  1. That's interesting about the commandbars being workbook-specific. It's also answers, I think, a question that came up for me recently. I was working on my MenuRighter addin to make it stay on top of all open workbooks in Excel 2013, using some code I found on JKP's site. I got it to work, but then noticed that the addin's basic functionality was broken when I switched between workbooks.

    MenuRighter allows one to copy Excel 2003 menu commands onto right-click menus. To do so, the code creates temporary commandbars and copies to and from them. When I switch to a new workbook the code recognizes the temporary commandbars, but errors (with one of the vaguer, less-helpful VBA error messages) when I try to copy from them. So maybe the code still sees the object, but it's not actually functioning across the workbook interfaces.

    ReplyDelete
  2. Maybe looping through open windows and activating them before running the code might help? It helped get ridding of icons (usually?) as the link showed, but I found the need to close and reopen everything, when installing addins, not an acceptable solution to ask of my people who download my stuff.

    Anyway, there seems to be a lot going on with the new Ribbon under the hood. I'm very glad I can open files in their own Window now. None of the "fixes" worked for me with 2007 and 2010 (at least not well). But I also experience some odd behavior at times. Yesterday I was getting "file is read-only" messages when I tried opening them. Only happens at random. Close everything and reopen = problem gone. The "other user" who had them open was apparently me. Hmm...

    But did I mention how happy I am to open workbooks in their own Windows? Yay!

    ReplyDelete