PDA

View Full Version : [SOLVED] Return value from Custom Ribbon



ashleyuk1984
12-07-2015, 04:55 AM
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.

http://ultraimg.com/images/Capture6c1a4.jpg

Drop downs...

http://ultraimg.com/images/Captured1859.jpg

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
12-07-2015, 05:59 AM
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
12-07-2015, 05:45 PM
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
12-07-2015, 05:57 PM
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
12-08-2015, 01:16 AM
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
12-10-2015, 03:18 AM
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!! :)