如何在Excel自定义功能区控件的下拉控件中设置默认值

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

我为Excel 2010创建了一个自定义Fluent功能区界面，其中包含一个下拉列表。相关的XML代码（简化）：

<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:

我希望默认情况下选择第一个项目 - 但找不到任何描述如何操作的文档。我查看了控件的MSDN文档，但它没有涵盖这种情况。我尝试了各种“HTML-like”语句的排列，但它们都被自定义UI编辑器拒绝为无效。我试过的事情的例子：

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

Error message: The 'selected' attribute is not declared

错误消息：未声明“selected”属性

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

我在 声明中尝试了其他属性，如selectedItem，value和selected，但似乎没有任何效果。

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.

如果我只有正确的文档，这将是微不足道的，但即使是功能区自定义的完整Microsoft“文档”（在这里找到了关于这个主题的沉默。

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

我甚至试图查看位于http://schemas.microsoft.com/office/2006/01/customui的架构是否“人类可读”，但当我尝试在浏览器中打开它时，我被告知它是不可用。也许有一招......

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

所以我转向这个论坛的综合智慧。您可以从我的Q / A比率中看出我不经常这样做...

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.

如何修改我的XML以便打开功能区，并在下拉控件中选择任意项？我会满足于它是第一个项目 - 但“我选择在我的XML中声明的任何项目”将更可取。

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

我正在为此寻找一个XML解决方案 - 宁愿不必添加onLoad VBA代码或其他VBA技巧。这有多难，对吧？......

3 个解决方案

#1

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

看起来您需要使用VBA才能选择默认项目。

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

引用dropDown元素的文档（我的重点）：

getSelectedItemID (getSelectedItemID callback)

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:

指定要调用的回调函数的名称，以确定要在此控件中选择的项的标识符。 getSelectedItemID和getSelectedItemIndex属性是互斥的。如果两个属性都未指定，则控件不应显示所选项目。例如，请考虑以下XML片段：

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

在此示例中，当应用程序需要确定库中的选定项时，将调用GetGallerySelectedItemID回调函数。在此示例中，回调函数返回GetItemID回调函数返回的标识符之一。此属性的可能值由ST_Delegate简单类型定义，如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.

根据我对文档的阅读，您需要自己维护过滤器的当前所选项目。 GetSelectedItemID处理程序将返回当前选定的项，OnAction处理程序将更新它。

In the XML:

在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

#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).

我在启动时出现了类似的问题，因为还没有设置任何内容。但是，当控件失效但dropDown已经填充时，它将再次返回空白选择（我使控件无效，因为我在列表中添加了一些新项目，所以我希望它重建）。

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:

然后VBA回电：

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

Sub GetSelectedItemIndexDropDown（控制为IRibbonControl，ByRef索引）'XML文件中的Callbackname“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.

按预期工作。注意：onAction =“onActionCallback”用于设置状态并将其广播给VBA中的任何人; getSelectedItemIndex =“onGetSelectedItemIndexCallback”用于功能区查询它应显示的状态。

#3

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

我无耻地欺骗了这个XML - 我使用了RibbonCreator 2010。

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

DefaultValue似乎是在所有荒谬地方的dropDown标签中设置的......

<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:

除非您将以下函数添加到VBA代码，否则这将无效：

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.

但是，它可以做得足够通用，一旦它就位，就可以用XML重复引用。