VBA direct access to ribbon element values

Question

How can I access to my custom ribbon elements?

The elements are made in (.xlsm\customUI\customUI.xml)

<ribbon startFromScratch="false">
    <tabs>
        <tab idMso="TabHome">
            <group id="MatrixGroup" label="xxx" insertBeforeMso="GroupClipboard">
                <button id="b1" label="111" imageMso="DataFormSource" onAction="asas" />
                <button id="b2" label="222" imageMso="ConditionalFormattingClearMenu" onAction="sasa" />
                <dropDown id="Drop" label=" Env" sizeString="WWWWWWWWW">
                    <item id="Item1" label="1"/>
                    <item id="Item2" label="2"/>
                    <item id="Item3" label="3"/>            
                    <item id="Item4" label="4"/>
                </dropDown>
            </group>
        </tab>
    </tabs>
</ribbon>

I need to get chosen dropDown element name/id when I click by button id="b1" so onAction="myMacro" for the dropDown element can not help here.

But anyway there is a code to get dropdown element id - maybe you can transform it to call this sub from another Sub (which is triggered by pressing button id="b1")

Sub GetS(control As IRibbonControl, id As String, index As Integer)
    If control.id = "Drop" Then
    MsgBox id
End If End Sub

score 1 · Accepted Answer · edited May 23 '17 at 10:30

There are several callbacks you might use in this case:

GetSelectedItemIndex: I think this returns the index position of the Selected Item from Dropdown control.
GetItemLabel: Returns the selected item label from a ribbon dropdown control
GetItemID: Returns the ID of the specified Index item

The signatures for each are:

Sub GetSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)

End Sub

Sub GetItemLabel(control As IRibbonControl, Index As Integer, ByRef returnedVal)

End Sub

Sub GetItemID(control As IRibbonControl, Index As Integer, ByRef id)

End Sub

You're correct that you can pass a control object from one callback to another (essentially manually invoking/triggering the callback procedure for these).

I'm not in any position to test this, so I'm going a bit from memory and a bit from guesswork, you may need to tweak this and debug on your own, but hopefully this is helpful. The way thiswould need to work, if I remember correctly, is when the sasa callback is invoked, you need to manually call GetSelectedItemIndex and then GetItemLabel, passing the appropriate control object.

That's the tricky part, keeping a handle on the dropDown control object.

Practically speaking, there is some difficulty with Ribbon object, and I found it was more easy to work with by loading it initially into a custom class object (retaining direct object references to each of the controls in a Dictionary property, keyed by control name/id) so that I could pass them at will from other procedures). There is some background on this here which might be enough to go on, in terms of setting up the Class object, you'll just need to add another property Get and Let procedure as a Dictionary type so you can add each ribbon control to the dict. If you have trouble with that implementation, please ask a separate question on that and I'll try to help.

So, once you can access the appropriate control, you pass that object to the GetSelectedItemIndex e.g.:

Dim ctrl as IRibbonControl
Dim itm_id, itm_index 
Set ctrl = {some object reference to the dropDown control}

Call GetSelectedItemIndex(ctrl, itm_index)
Call GetItemId(ctrl, itm_index, itm_id)
MsgBox(itm_id)

VBA direct access to ribbon element values

1 Answers1