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?...

Solution 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

Solution 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.

Solution 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.

Comments

[Floris]

Well - unfortunately I can't easily test this right now (I am at home and the target laptop was left at work) but if this works I will be both astonished (really? Put the value in a TAG???) and very grateful! Equal measures of both.

[Floris]

The format of the tag makes me wonder if an attribute of DefaultValue="1" might work... Too bad (in this instance) that my home computer is a Mac - and Office for the Mac doesn't have ribbon customization. One of many things that Microsoft just can't get right.

[Monty Wild]

My personal programming philosophy is: "when in doubt, cheat shamelessly". I've never written ribbon XML before, but I knew of the existence of these ribbon editors. Any decent programmer can take the output of even a trial version and make it do what they want. That said, I'm rather incredulous myself that the value goes in the tag. (What on earth was MS thinking if this is correct!) This may also be application specific, and you haven't said if you're using Access, Excel or something else...

[Tmdean]

I just tested it and it doesn't work. Does RibbonCreator generate VBA code? I think RibbonCreator is generating boilerplate VBA code that uses the tag element to determine its behavior.

[Monty Wild]

If this actually works, the credit should really go to Gunter Avenius, but since I can't find him here on SO (and he didn't answer your question), I'll be happy to take any credit anyone cares to give me on his behalf ;)

[Floris]

@Tmdean - I suspect you are right. A tag ought never to be something that is interpreted as an XML instruction. I am suspecting that the correct answer will end up being "it can't be done" but I'm not giving up hope so quickly. Right now I'm trying to find a way to hack into the schema...

[Monty Wild]

Tmdean was right - this is achieved through VBA callbacks, but since you have to have them to get your own functions to work, what's the problem in having a few extra to handle ribbon element interaction? Tag did seem to be a ridiculous place for MS to put this.

[Tmdean]

Yeah, that's similar to my answer, but I think you need to show the OnAction handler. Unless I'm not reading the code correctly, GetSelectedItemIndexDropDown will always return the same value in this implementation, so it will be impossible to change the value of the dropdown.

[Monty Wild]

Tmdean, GetSelectedItemIndexDropDown appears to be called when the dropdown is created, and the OnAction handler handles user actions separately. Since the question was about setting a default value, this answers the question, albeit with the unavoidable necessity of VBA.

[Tmdean]

I just tested the code again and you indeed can't change the value of the dropdown. It sets the default value correctly (to "b"), but you can't change it to "a". dl.dropboxusercontent.com/u/2784079/Test.xlsm

[Monty Wild]

So, you'd need to use the OnAction callback to change the value returned by GetSelectedItemIndexDropDown for the relevant dropdown. They could still be fairly generic pieces of code, if not quite so simple.

[Tmdean]

Yeah, take a look at my answer.

[Monty Wild]

So, the correct answer will be somewhere between yours and mine. Unless Floris can't handle the extra VBA, there's no need to add any more to our answers or comments.

[Monty Wild]

This requires the default be set in VBA, mine allows the default to be set in XML even though both require VBA.

[Floris]

You are arriving at the same conclusion that I got to... that "it can't be done". I actually found a human readable copy of the schema and went through it line by line. No sign of "default" or any such attribute. So back to VBA it is. Clearly at that point there are many options. Still annoyed at that empty box that is staring at me until I choose to select something...

[Floris]

I can handle the VBA - no problem. I was looking for "some XML magic that I had missed" - more from a purist's prospective than because I was desperate. I am forever mystified by the things that Microsoft simply chose not to implement / expose; and that empty combobox just grates. I guess they are expecting everyone to use dynamic methods for populating the drop down. I might have to resort to that. Meanwhile I thank you both very much and will leave the question "unanswered" to see if anyone in another timezone has any ideas. Cheers!

[Floris]

Now that I have been mulling this over for a while I am actually beginning to understand what is going on. When the ribbon needs to draw the DropDown, it will call the GetSelectedItemID function; by returning the desired state, we can force the display of the default value. And the annoying empty box is gone. Thanks for your help!