Ribbon's equivalent to CommandBars.ActionControl

[Paul Martin]

With pre-Excel 2007, I could ascertain what user-selection was made
with a custom toolbar menu item using
CommandBars.ActionControl.Index. This menu would be populated from a
worksheet range and knowing the index using this syntax would enable
me to select the cell number from that range.

Now I'm trying to do the equivalent with the Ribbon in Excel 2007. I
can see how to return the control's id property but not the
selection's index. Any suggestions?

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Paul Martin]

OK, here's more on what I'm trying to do:

I have some Menu items I've added to the Ribbon, and each Menu item
has multiple Button items. When I click a Button, I want to be able
to capture that item. Ideally, that'd be its 'index' on the Menu and
also it's description or label. I've been playing around with the
Callbacks, such as getDescription, but I just can't work it out. Any
suggestions are greatly appreciated.

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Andy Pope]

Hi Paul,

You can use the OnAction call back of the buttons in your menu. The can each
use the same callback routine.
Within the callback routine you can test the control.id or control.tag in
order to determine what was clicked.

I don't think you can read the buttons text. You would need to know that
before hand and some how match it to the controls id.

Cheers
Andy

 

[Paul Martin]

Hmmm, it seems the functionality of these ribbon buttons is somewhat
dumbed down compared to Excel 2003 toolbar controls. In that, I
could, for example, use .Parent.Parent to ascertain which menu item
was selected, and also the selection's index would identify cell had
populated the button's value. I don't find control.id or control.tag
to be particularly useful.

Thanks for your help, Andy.

Paul

 

[Paul Martin]

As an addendum, I've been playing around with the button's Callbacks.
I thought getDescription might be useful, but I can't work out how it
is used, what it is used for. Can anyone enlighten me?

TIA

 

[Andy Pope]

If you have not already have a read of Stephen Bullen's chapter on Ribbonx
http://www.oaltd.co.uk/Excel2007ProgRef/Default.htm

The getXXX callbacks are, to me at least, a bit misleading in their name.
The do not get/return anything to you as the programmer. Instead they
get/return information to the Ribbon. So you use it to control the
Description information that the control uses. Rather than as the name
suggests get the description information from the control.

And yes the VBA functionality of the ribbon is limited compared to the
Commandbar object model. That said the ribbon can be much easy to produce
and maintain than commandbar coding *if* you are using controls that already
exist. So to add a fully functioning button to make stuff bold requires only
a reference in the xml to the idMso. Plus creation and destruction of the
ribbon is automatic when the workbook opens and closes. Also when it
activates or deactivates the ribbon controls are automatically
removed/returned.

Cheers
Andy