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.
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.
ReplyDeleteMenuRighter 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.
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.
ReplyDeleteAnyway, 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!