Return value from Custom Ribbon

[ashleyuk1984]

Hi,
I've basically created a custom ribbon using xml and this loads as an excel add-in.

On the ribbon, I have a drop down box in which I can choose one of three options.
I need to return that chosen value to a subroutine.

How can I make a macro detect the value in the custom ribbon?

This is what the ribbon looks like - quite basic at the moment.



Drop downs...



And this is the XML code for it.

<!--RibbonX Visual Designer 2.31 for Microsoft Excel CustomUI . XML Code produced on 2015/12/07--><customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
    <ribbon >
        <tabs >
            <tab 
                id="Tab1"
                label="Control Gallery 2">
                <group 
                    id="Group2"
                    label="Drop Down">
                    <dropDown 
                        id="Dropdown1"
                        label="Text Items"
                        sizeString="WWWWWWWWWWW">
                        <item 
                            id="Item10"
                            label="Item10"/>
                        <item 
                            id="Item11"
                            label="Item11"/>
                        <item 
                            id="Item12"
                            label="Item12"/>
                    </dropDown >
                </group >
            </tab >
        </tabs >
    </ribbon >
</customUI >

[Aflatoon]

The onAction callback supplies the selected item (the id argument) so you either call the macro from that or populate a variable that is accessible by the macro.

[ashleyuk1984]

Ahhh I see ok thanks.
So now I'm able to return the value to the macro, which is what I wanted.

Here's the next question.
I have three dropdowns - these represent year / month / customer, and I would like to return the values from all three into the one macro.
The first two selections shouldn't fire any macros, but the third dropdown should fire it and then process the code.

However, heres the problem.... I'm only getting the values from the final dropdown.
How do I get access to the other two?

XML Code:

<group id="BookmarkGroup" label="Bookmarks">
<dropDown id="dd01">
<item id="cboItem1" label="Item 1"/>
<item id="cboItem2" label="Item 2"/>
<item id="cboItem3" label="Item 3"/>
</dropDown>
<dropDown id="dd02">
<item id="cboItem4" label="Item 4"/>
<item id="cboItem5" label="Item 5"/>
<item id="cboItem6" label="Item 6"/>
</dropDown>
<dropDown id="dd03" onAction="dd01OnAction">
<item id="cboItem7" label="Item 7"/>
<item id="cboItem8" label="Item 8"/>
<item id="cboItem9" label="Item 9"/>
</dropDown>
</group>

VBA Code:

Sub dd01OnAction(control As IRibbonControl, ID As String, index As Integer)
Debug.Print "The value is " & ID
End Sub

This currently returns either cboItem7 / 8 or 9 ... I can't get access to the other two selections. Do I need to make the other dropdown boxes some sort of public function or something? I'm not 100% sure how to do this.

This is what I'm using at the moment, it's just dummy data, but once I get it working then I'll change the values to suit my needs.
Thanks for your help.

[Paul_Hossler]

Dropdowns have a special onAction callback that return the index of the selected item

You can use the same for all 3 drop downs and Select Case on control.ID

Sub ddOnAction(control As IRibbonControl, id As String, index As Integer)
    Select Case control.id
        Case "dd01"
            MsgBox "Drop Down#1 (" & control.id & ") MenuID = " & id & " Item# = " & index
        Case "dd02"
            MsgBox "Drop Down#2 (" & control.id & ") MenuID = " & id & " Item# = " & index
        Case "dd03"
            MsgBox "Drop Down#3 (" & control.id & ") MenuID = " & id & " Item# = " & index
    End SelectEnd Sub

[Aflatoon]

You need an onAction callback assigned to all the dropdowns, even if the first two do nothing more than store their values in variables accessible to the sub you want to call.

[ashleyuk1984]

Thank You.
I put onActions on all three dropdowns, and simply stored the values as variables as you suggested, and made them Public.
On a separate button (The 'execute' button - if you'd like to call it that), I called the previously stored variables, and the extra code that I wanted to execute. Worked perfectly fine.
Thanks for your input!!