Dynamic Ribbon control in XL2007

[Martin Quinn]

I am in the process of converting a bunch of Xl2003 *.xls for use with Xl2007 as *.xlsm files and have made considerable progress in making the original code compatible with the changes introduced in Xl2007, like the reduced functionality of WORDART under VBA control.

A major feature of the Xl2003 files is the substitution of the standard menu bar with a custom command bar displaying only the command buttons to initiate subroutines for the different calculations that can be carried out. During operation of these files certain buttons are rendered invisible/visible so that the user path is restricted to prevent problems arising during operation of the files. I have replaced all the command bar buttons with buttons defined by the Ribbon and I have used the startFromScratch instruction together with disabling of the Office menu to hide as much of the normal controls as possible from the casual user.

Using Chapter 22 of John Walkenbach's latest book and the model of his 'dynamicmenu.xlsm' file for that chapter, I have managed to apply the Worksheet_Change event handler to refresh the ribbon and modify the 'visible' property of different buttons in the dropdown menu that results.

Here is a little of John's coding.

'********************************************************************
For the customUI file he has

customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
onLoad="ribbonLoaded">
<ribbon>
<tabs>
<tab id="CustomTab" label="Dynamic">
<group id="group1" label="Dynamic Menu Demo">
<dynamicMenu id="DynamicMenu"
getContent="dynamicMenuContent"
imageMso="RegionLayoutMenu"
size = "large"
label="Sheet-Specific Menu"/>
</group>
</tab>
</tabs>
</ribbon>
/customUI>

'*******************************************************************
and then, for example, the code

menu xmlns="http://schemas.microsoft.com/office/2006/01/customui"
itemSize= "large">
<button
id="b1"
imageMso="AutoDial"
label="View the phone log"
onAction="Macro1" />
<button
id="b2"
imageMso="AttachMenu"
label="Order office supplies..."
onAction="Macro1" />
<button
id="b3"
imageMso="BlogHomePage"
label="Quit for the day and go home"
screentip="Had enough?"
supertip="Click here if you would like to knock off early today. Your boss will be alerted, and he will reply via email within 10 minutes."
onAction="Macro1" />
/menu>

'*********************************************************************

I have the following 2 problems. Why do I need the first and last line of the above dynamic content, wherein the word 'menu' appears? Is it possible to have the buttons in the ribbon space, not in a drop down menu, and yet retain the dynamic property?

The preview of this message does not display very well and I had to remove the leading and finishing < tags to get even this amount displayed, so I hope it makes sense!

 

[tusharm]

To have complete control over the Ribbon (and not just the dynamic menu items), you must use a COM Add-In. The MS developers who came up with the Ribbon concept did not (do not?) consider VBA a real development language.

That said, if you know beforehand all the controls you are likely to need, you can add them through the XML and the use the getVisible (and/or getEnabled) property to control the ribbon.