How to set default value in dropdown control for Excel custom ribbon control

Question

I have created a custom Fluent Ribbon interface for Excel 2010 which includes a dropdown. Relevant XML code (simplified):

<dropDown id="chooseFilter" showLabel="true" label="Filter" onAction="filterSelected" > 
    <item id="Filter1" label="Filter 1" /> 
    <item id="Filter2" label="Filter 2" /> 
</dropDown>

When the ribbon is loaded, no value is selected - the dropdown looks empty.

I would like the first item to be selected by default - but could not find any documentation describing how to do it. I looked at the MSDN documentation for the control but it did not cover this case. I tried various permutations of "HTML-like" statements, but they were all rejected by the custom UI editor as invalid. Examples of the things I tried:

<item id="Filter1" label="Filter 1" selected="selected" /> 

Error message: The 'selected' attribute is not declared

I tried other attributes like selectedItem, value, and selected in the <dropDown .../> declaraction, but nothing seemed to work.

If only I had the right bit of documentation this would be trivial, but even the full Microsoft "documentation" for the Ribbon customization (found here was silent on the subject.

I even tried to see if the schema located at http://schemas.microsoft.com/office/2006/01/customui might be "human readable", but when I tried to open it in the browser, I was told it was unavailable. Maybe there is a trick...

So I turn to the combined wisdom of this forum. You can see from my Q/A ratio that I don't do this very often...

How do I modify my XML so that the ribbon opens with an arbitrary item selected in the drop-down control? I will settle for it being the first item - but "any item I choose to declare in my XML" would be preferable.

I am looking for an XML solution for this - would prefer not to have to add onLoad VBA code or other VBA trick. How hard can it be, right?...

Answer 1

It looks like you need to use VBA in order to select a default item.

Quoting from the documentation for the dropDown element (my emphasis):

getSelectedItemID (getSelectedItemID callback)

Specifies the name of a callback function to be called to determine the identifier of the item to be selected in this control. The getSelectedItemID and getSelectedItemIndex attributes are mutually exclusive. If neither attribute is specified, the control SHOULD NOT display a selected item. For example, consider the following XML fragment:

<gallery id="gallery" getItemCount="GetGalleryItemCount"  
   getItemID="GetItemID"
   getSelectedItemID="GetGallerySelectedItemID" />

In this example, the GetGallerySelectedItemID callback function is called when the application needs to determine the selected item in the gallery. In this example the callback function returns one of the identifiers returned by the GetItemID callback function. The possible values for this attribute are defined by the ST_Delegate simple type, as specified in section 2.3.2.

According to my reading of the documentation, you're expected to maintain the current selected item of the filter yourself. The GetSelectedItemID handler will return the currently selected item and the OnAction handler will update it.

In the XML:

<dropDown id="chooseFilter" showLabel="true" label="Filter"
   getSelectedItemID="GetSelectedItemID" onAction="OnAction"> 
   <item id="Filter1" label="Filter 1" /> 
   <item id="Filter2" label="Filter 2" />
</dropDown>

And in a code module of your workbook:

Private mCurrentItemID As Variant

Sub GetSelectedItemID(control As IRibbonControl, ByRef itemID As Variant)
    If IsEmpty(mCurrentItemID) Then
        mCurrentItemID = "Filter1"
    End If
    itemID = mCurrentItemID
End Sub

Sub OnAction(control As IRibbonControl, selectedID As String, _
             selectedIndex As Integer)
    mCurrentItemID = selectedID
End Sub

Answer 2

I had a similar problem with the blank drop down at startup, as nothing was set yet. However, when the control was invalidated but the dropDown was already populated, it would again return the blank selection (I invalidated the control because I added some new items to the list, so I wanted it rebuilt).

The solution, as mentioned here, is to use the
<dropDown id="ddc0" label="Label Dropdown 0" getSelectedItemIndex="GetSelectedItemIndexDropDown ... as mentioned.

And then the VBA call back:

Sub GetSelectedItemIndexDropDown(control As IRibbonControl, ByRef index) ' Callbackname in XML File "GetSelectedItemIndexDropDown ...

Worked as expected. Note: the onAction= "onActionCallback" is used to set the state and broadcast it to whoever in VBA; the getSelectedItemIndex= "onGetSelectedItemIndexCallback" is use for the ribbon to query the state that it should be displaying.

Answer 3

I cheated shamelessly to get this XML - I used RibbonCreator 2010.

The DefaultValue appears to be set in the dropDown's tag of all the ridiculous places...

<dropDown id="ddc0" label="Label Dropdown 0" getSelectedItemIndex="GetSelectedItemIndexDropDown" onAction="OnActionDropDown" getVisible="GetVisible" getEnabled="GetEnabled" tag="RibbonName:=;inMenu:=;CustomTagValue1:=;CustomTagValue2:=;CustomTagValue3:=;DefaultValue:=1;CustomPicture:=;CustomPicturePath:=">
    <item id="ddc0Item0" label="a" screentip="a" supertip="a"/>
    <item id="ddc0Item1" label="b" screentip="b" supertip="b"/>
</dropDown>

EDIT:

This won't work unless you add the following functions to your VBA code:

Sub GetSelectedItemIndexDropDown(control As IRibbonControl, ByRef index)
    ' Callbackname in XML File "GetSelectedItemIndexDropDown"
    ' Callback getSelectedItemIndex
    Dim varIndex As Variant
    varIndex = getTheValue(control.Tag, "DefaultValue")
    If IsNumeric(varIndex) Then
        Select Case control.ID
            ''GetSelectedItemIndexDropDown''
            Case Else
                index = getTheValue(control.Tag, "DefaultValue")
        End Select
    End If
End Sub

Public Function getTheValue(strTag As String, strValue As String) As String
   Dim workTb()     As String
   Dim Ele()        As String
   Dim myVariabs()  As String
   Dim i            As Integer
   On Error Resume Next
      workTb = Split(strTag, ";")
      ReDim myVariabs(LBound(workTb) To UBound(workTb), 0 To 1)
      For i = LBound(workTb) To UBound(workTb)
         Ele = Split(workTb(i), ":=")
         myVariabs(i, 0) = Ele(0)
         If UBound(Ele) = 1 Then
            myVariabs(i, 1) = Ele(1)
         End If
      Next
      For i = LBound(myVariabs) To UBound(myVariabs)
         If strValue = myVariabs(i, 0) Then
            getTheValue = myVariabs(i, 1)
         End If
      Next
End Function

However, it could be made sufficiently generic that once it was in place, it could be referred to repeatedly in XML.