w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How to set default value in dropdown control for Excel custom ribbon control

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"
onAction="OnActionDropDown" getVisible="GetVisible" getEnabled="GetEnabled"
    <item id="ddc0Item0" label="a" screentip="a" supertip="a"/>
    <item id="ddc0Item1" label="b" screentip="b" supertip="b"/>


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
            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
      For i = LBound(myVariabs) To UBound(myVariabs)
         If strValue = myVariabs(i, 0) Then
            getTheValue = myVariabs(i, 1)
         End If
End Function

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

© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.