Dynamically sized menu in customised Ribbon tab

[mc84excel]

I am trying to create a dynamically sized menu in a customised Ribbon tab (where the number of macros to include on the menu is defined in VBA not XML) and I can not get this to work.

It might be simpler to explain what I am after by demonstrating a similar concept (ignoring the customised tab aspect): I can create a XLAM containing grouped controls which appear in the Add-Ins tab. The number of macros to include the group are dynamically bound (sample below):

Popup1MacNames = Array("Macro1", "Macro2", "Macro3", "Macro4")

With Office.CommandBarControl
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.Caption = "Caption1"
.BeginGroup = True
.Tag = C_TAG
For iCtr = LBound(Popup1MacNames) To UBound(Popup1MacNames)
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.OnAction = "'" & ThisWorkbook.Name & "'!" & Popup1MacNames(iCtr)
.Caption = Popup1MacNames(iCtr)
.Tag = C_TAG
End With
Next iCtr
End With


As can be seen with above code, to link a new macro to this group, all I need to do is type the sub name into the Array line.

Now I wish to do the equivalent in a customised ribbon tab. And I need to define the size of the menu in VBA, not in XML.

I am already using Bob Phillips Dynamic Ribbon code - I need to add a menu to contain a list of macros. And the ability to easily add extra macros to this menu without having to edit the XML every single time.

[Bob Phillips]

The way that I do it is to have a control of type dynamicMenu in the XLM. This control invokes a callback that I call rxGetContent. This callback returns XML that defines the menu that will be displayed. This is my code for rxGetContent, which in turn calls a function to build the XML. This function is passed an id, to differentiate this app, a matrix that holds all of the properties of the menu items, and an image to show alongside the menu items.

Please Login or Register  to view this content.

Note that CONTROLID_RB_DYNA_MENU is a public constant where I store the dynamic menu control name that I assign in the XML.

The matrix has as many rows as you have items, and 4 columns, the item name (which is appended to the function id parameter, the item label, the macro that is called if the item is clicked, and a tag property for the item which allows a parameter value if required. This matrix needs to be setup as the workbook opens, and would be of the form

Please Login or Register  to view this content.

Finally, this is the code that builds the XML

Please Login or Register  to view this content.

NAMESPACE_RIBBON_2007 is another public constant with the value

Please Login or Register  to view this content.

[mc84excel]

I didn't expect to get a reply from Bob Phillips himself! Thank you! I will try this out within the next 24 hours to see how it works for me and post an update.

[Bob Phillips]

Good luck with it, I will keep an eye on the thread in case you have any follow-up questions.

[mc84excel]

After entering the code, the result is much closer to what I am after but clicking on the menu dropdowns doesn't run the macros. (I have only learnt about custom ribbons recently so please be patient with any newbie errors). I will try uploading the workbook.

[Bob Phillips]

I think I was missing a few key points in my original reply, so let's try plugging them.

First, we should be routing the menu item onAction through a callback routine, just as with any other ribbon control. It can be its own callback, or even a callback per item but this reduces the dynamicness of our code, so for the sake of this exercise I will route it through our generic rxOnAction. If we do use this, we have to know the macro that each item will invoke, so I will stick that in the tag element. Thus, our array will now look like this

Please Login or Register  to view this content.

We just need a small change now so that the rxOnAction routine knows about these items. We could add each menu item in our Case statement, but again that reduces our ability to add items easily, so I will just look for the button start text (as they are all similar). So in the globals area we add another constant

Please Login or Register  to view this content.

and finally we enhance rxOnACtion to test for this and run the macro identified in the tag property

Please Login or Register  to view this content.

You should also add a label callback to that dynamic menu, so that you can see it in the ribbon group.

BTW, where did you get this code. Clearly I must have posted it somewhere, but I do not recall posting it, and think I should update it to have a menu example as well.

[mc84excel]

Thank you. I will try implementing the changes and respond within the next 24 hours.

In regards to the question as to where I got the code: I read your article 'Who Says the Ribbon is Hard?' on http://msmvps.com/blogs/xldynamic/ar...n-is-hard.aspx
However the download link on this page isn't working. I made contact with a site admin who provided me with the workbook uploaded on skydrive.live.com

The code will look different to how you left it as I have tried experimenting with adding a dynamically sized menu on the ribbon as well as trying to implement the 'Loss of Ribbon State' concept. The 'Loss of State' recovery I can not manage to get to work 100% with the 'Dynamic Ribbon' unfortunately, however that is probably a topic for another thread rather than taking this one off on a tangent?

If you upload an updated 'Dynamic Ribbon' workbook, I would be very interested in seeing it.

[Bob Phillips]

I'll do another blog post with the updated workbook, hoepfully within a week. I can refer to the other post and also add a comment to that one to try and keep the links current.

[mc84excel]

It is almost there. I now have two dynamically sized menus running in the workbook. However if I hide the Dynamic tab and unhide again (see buttons on Scratch tab), the dropdown menus appear to lose their links? Could this be something to do with the tag?

[Bob Phillips]

Either I am mis-understanding what you are saying, or I am not seeing this problem.

I clicked item 1 in both menus and it gave me the MsgBox as expected. I then hid the tab, and unhid it. Clicking item 1 in each menu gave me the appropriate MsgBox.

Is this what you meant?

[mc84excel]

Sorry my fault - I missed a key point. I was testing for recovery from Loss of State before hiding/unhiding. (Run the macro TestRibbonForLossOfState. Test the Dynamic Ribbon tab. The two buttons will still work. The two dropdown menus fail).

I assume this occurs because the mgMtxMenu arrays are set up on the RibbonOnLoad? (Please be patient with any obvious questions I ask - the whole concept of a customised ribbon is new to me).

[mc84excel]

What is the best method of ensuring the Dynamic menus will recover after a Loss of State? Should I create a separate sub for setting up the mgMtxMenu arrays and call this from the subs: RefreshRibbon & RibbonOnLoad?

[Bob Phillips]

I haven't tested loss of state myself, but that would be my guess that as you summise the variable has gone out of scope. My first attack would be along the lines that you suggest.

Give it a whirl, and if that doesn't cure it we can look a bit deeper. BTW, how did you force a loss of state?

[mc84excel]

OK I will try further testing/code changes over the weekend and post an update next Tuesday.

Re: Loss of State. I forced this by causing a divide by 0 error (see Macro "TestRibbonForLossOfState"). I took this from the workbook on Ron de Bruins site: http://www.rondebruin.nl/ribbonstate.htm (Ron de Bruin based this on an idea by Rory Archibald. I'm trying to merge this Loss of State code into the Dynamic Ribbon so I can overcome the biggest weaknesses of customising the Ribbon - flexibility and stability).

[mc84excel]

I think I now have the Dynamically sized menus working the way I want them to (the code could do with some tidying + tightening of scopes but that's no major issue). See attached workbook FYI.

Thank you for all your help with this project.

I would be very interested in seeing an updated version of the Dynamic Ribbon workbook some day.

[mc84excel]

Spoke too soon. I am still having issues with Loss of State conflicting with the dynamically sized menus.

If I open the workbook and trigger a loss of state (using the 'Test Ribbon for...' button), then click each of the menus, the dropdown lists usually appear. (Sometimes they are lost)

However if I ever 'show' the ribbon (by using the Show Dyna Tab button) and then trigger a loss of state, the menu dropdowns are always lost.

Why does this happen? Is it something to do with the tags?

(After the menu lists are lost, you can manually fix it by clicking the 'Hide Dyna Tab' followed by 'Show Dyna Tab'. But I would rather prevent it happening in the first place).

[mc84excel]

I solved the issue of the dynamic menus losing their array dropdowns ~ 2-3 days ago. I am uploading a workbook to show my solution.

[mc84excel]

The latest uploaded version of the workbook discussed on this thread is found here: http://www.excelforum.com/excel-prog...-recovery.html