An alternative to the Add-Ins ribbon tab - Global Custom Ribbon Tab by Ken Puls

Lately I'm trying to create a custom ribbon tab that can store controls from multiple add-ins.

I have a number of add-ins I created in the past and - assuming this can be solved - this will be the best way to display multiple add-in controls at once.
(The other alternatives are to: a) abuse the built-in 'Add-Ins' tab - which quickly gets cluttered with multiple dropdowns & buttons; or b) have a custom ribbon tab for each add-in - which clutters up the ribbon with excess tabs instead).

This dream has (almost) been solved by the indefatigable Ken Puls. See his article at: http://www.excelguru.ca/blog/2007/03...ong-workbooks/

I follow the article instructions then make the following changes.

I open Leech.xlsm with 'Custom UI Editor for Microsoft Office' and change the 'Leech' references to 'Leech1' and save. I then open & save the XLSM as an add-in (leech1.xlam) and load it.
I then repeat the above but this time I change the 'Leech1' to 'Leech2' in XML and save it as another add-in ('leech2.xlam')

I check the UI Test tab and at first it appears to be working. But when I click on the buttons, the call backs from the leech groups are both going to leech2 add-in.

1. I suppose I could customise the callback name for every add-in but that would be a nuisance (and slight risk of doubling up) - isn't there a better way?
2. It throws me that there is no ribbon object set. What if I want to invalidate a specific group? (I tried adding it to one of them but it didn't work)
3. As there is no ribbon object - how would I set a loss of ribbon state recovery?
4. I take it (from a few brief experiments made) that it would be very difficult (if not impossible) to get dynamic menus running for any add-in using the global ribbon frame?

1. If you want to restrict the buttons to a specific workbook, include the project and module names in the onAction attribute. (I assume you are giving your projects better names than VBAProject)
2/3. There's no onLoad callback in Ken's article so it's not surprising.
4. Why, specifically?

Originally Posted by romperstomper

1. If you want to restrict the buttons to a specific workbook, include the project and module names in the onAction attribute.

I want the Leech buttons to appear available to all open workbooks (through means of the custom tab). However I want each Leech button to make its callback to its own XLAM.

I'm a XML newbie (as you can see). How would I write the onAction attribute in XML to include the project and module names? (BTW would the module name be necessary if I had the procedure set to project level scope?)

Originally Posted by romperstomper

(I assume you are giving your projects better names than VBAProject)

er... no. To date I have never found the need to give each project a custom name. I would do this if the above onAction required it to get this working.

Originally Posted by romperstomper

2/3. There's no onLoad callback in Ken's article so it's not surprising.

Agreed. I'm more thinking out loud as to why there isn't. Would adding an onLoad to each XLAM cause any conflicts with the overall Shared Tab concept? I mean, say I added onLoad to each XLAM as well as Loss of Ribbon State Recovery code (Ribbon programmers thank you Rory!) Now if the ribbon loses its state, what happens next? When they recover, would all the 'plug-in' XLAMs recover their controls back to the Shared Tab? Hmm I'm not expressing this very well. I should probably do some homework on this (after problem #1 is fixed) and come back with better questions.

Originally Posted by romperstomper

4. Why, specifically?

I apologise in advance for the reply I'm about to make.

No reason specifically!

There are various ribbon concepts I am enamored with (Loss of State Recovery, Dynamic Menus, Shared Ribbon Tab) and I am hoping these could all be combined to form the ultimate custom tab (If dynamic menus wont work with the Shared Tab then dont worry about it. Solving #1 to #3 are more important to me)

Simply:
onAction="projectname.modulename.macroname"

If all your projects are called VBAProject that's not going to work!

Re #4, I meant why specifically do you think it is difficult/impossible to use a dynamic menu on a shared tab?

Originally Posted by romperstomper

Simply:
onAction="projectname.modulename.macroname"

Thanks! +1
Seems a similar concept to calling a macro from another workbook. (Note to self - must try onAction="workbookname.modulename.macroname" to see if that would work as well)

Originally Posted by romperstomper

If all your projects are called VBAProject that's not going to work!

True that I'll amend my ways!

Originally Posted by romperstomper

Re #4, I meant why specifically do you think it is difficult/impossible to use a dynamic menu on a shared tab?

Welllll two reasons.

1. No onLoad in the add-ins. Surely onLoad is needed to set the ribbon object - which you would need in order to invalidate the ribbon when you change the controls on the ribbon?
2. As per previous post - my mental confusion as to what would happen if onLoad was added to the 'shared' 'plug-in' xlams and the ribbon control was lost. Would all these XLAMs restore to the shared ribbon? With the controls that they had before state was lost?

There's no onLoad because Ken didn't need one for what he was demonstrating, not because you can't put an onLoad into an add-in. The restored reference would be to the same object as before.

If you use workbookname rather than projectname, your ribbon will break if you save the workbook with a different name.

Originally Posted by romperstomper

There's no onLoad because Ken didn't need one for what he was demonstrating, not because you can't put an onLoad into an add-in. The restored reference would be to the same object as before.

OK I'll try doing this again later this week (Am tied up for next 2-3 days). Hopefully it will work better this time.

Originally Posted by romperstomper

If you use workbookname rather than projectname, your ribbon will break if you save the workbook with a different name.

Yes I know. That only occurred to me yesterday after I logged off the forum! Doh!
(That's also the reason why that I avoid referring to a worksheet by its tab name. If someone renames the sheet, the code will break)

Thanks Rory for your help. I've been playing around with this and finally got it working (After changing module scopes & inserting project.module for EVERY callback in the XML)

You were 100% correct (again). (Zip attached for the curious)

Two comments:

1. It bugs me that you need to specify the project.module for every single callback in the XML. I don't suppose that it would be possible to set these values as constants at the top of the XML?!
2. As you can see from the XLAMs in the zip - I am not using a fully dynamic menu in my custom tabs . I think I need some help on this. But that is off-topic of this thread so I will start a new one.