excelvbacomboboxhighlight

Excel Ribbon comboBox: How to set up a getSelectedItemIndex Callback


This is a follow-up question to this one:

Excel Ribbon comboBox: always highlight selection

The following code allows you to select from three paper sizes using a comboBox. There is only only issue: the selected item doesn't stay highlighted.

@OlleSjögren writes, that you need the getSelectedItemIndex-Callback to make this work.

Question: How do you build the getSelectedItemIndex-Callback?

If I add it to the XML, I get an error message: "the attribute is not declared".

Thanks!

' -- XML

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="LoadRibbon">
    <ribbon>
        <tabs>
            <tab id="Tabv3.1" label="TOOLS" insertAfterMso="TabHome">                                             
                <group id="GroupDemo2" 
                label="SelectPapersize"
                imageMso="AddInManager">
                    <comboBox id="ComboBox001"
                    label="comboBox001"
                    getText="ComboBox001_GetText"
                    onChange="ComboBox001_OnChange">
                        <item id="Item_A3"
                        label="A3"/>
                        <item id="Item_A4"
                        label="A4"/>
                        <item id="Item_A5"
                        label="A5"/>
                    </comboBox>
                </group>   
            </tab>
        </tabs>
    </ribbon>
</customUI>


' -- Callback VBA in Module "RibbonCallbacks"

Option Explicit
Public RibUI As IRibbonUI
Public Const myApp As String = "RibbApp", mySett As String = "Settings", myVal As String = "Value"

Sub LoadRibbon(Ribbon As IRibbonUI)
    Set RibUI = Ribbon
    RibUI.InvalidateControl "ComboBox001"
End Sub

'Callback for ComboBox001 onChange
Sub ComboBox001_OnChange(control As IRibbonControl, id As String)
    Select Case id
        Case "A3"
             ActiveSheet.PageSetup.PaperSize = xlPaperA3
        Case "A4"
            ActiveSheet.PageSetup.PaperSize = xlPaperA4
        Case "A5"
            ActiveSheet.PageSetup.PaperSize = xlPaperA5
    End Select
    RibUI.InvalidateControl "ComboBox001"
    SaveSetting myApp, mySett, myVal, id
End Sub

'Callback for ComboBox001 getText
Sub ComboBox001_getText(control As IRibbonControl, ByRef returnedVal)
  Dim comboVal As String
    comboVal = GetSetting(myApp, mySett, myVal, "No Value") 
    If comboVal <> "No Value" Then
      returnedVal = comboVal
    End If
End Sub


' -- VBA in "ThisWorkbook"

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim idx As String
    Select Case PaperSize
    Case "xlPaperA3"
        idx = "A3"
    Case "xlPaperA4"
        idx = "A4"
    Case "xlPaperA5"
        idx = "A5"
    End Select
    SaveSetting myApp, mySett, myVal, idx
    RibUI.InvalidateControl "ComboBox001"
End Sub

Solution

  • ' --- XML
    
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="LoadRibbon">
        <ribbon>
            <tabs>
                <tab id="Tabv3.1" label="TOOLS" insertAfterMso="TabHome">                                             
                    <group id="GroupDemo2" 
                    label="SelectPapersize"
                    imageMso="AddInManager">
                        <dropDown id="DropDown1"
                        label="DropDown1"
                        onAction="DropDown1_onAction"
                        getSelectedItemIndex="DropDown1_GetSelectedItemIndex"
                        >
                            <item id="Item_A3"
                            label="A3"/>
                            <item id="Item_A4"
                            label="A4"/>
                            <item id="Item_A5"
                            label="A5"/>
                        </dropDown>
                    </group>   
                </tab>
            </tabs>
        </ribbon>
    </customUI>
    
    ' -- Stardard module
    Option Explicit
    Public RibUI As IRibbonUI
    
    Sub LoadRibbon(ribbon As IRibbonUI)
        Set RibUI = ribbon
        RibUI.InvalidateControl "DropDown1"
    End Sub
    
    'Callback for DropDown1 onAction
    Sub DropDown1_onAction(control As IRibbonControl, id As String, index As Integer)
        Dim iSize As Long
        Select Case Right(id, 2)
            Case "A3"
                 iSize = xlPaperA3
            Case "A4"
                iSize = xlPaperA4
            Case "A5"
                iSize = xlPaperA5
        End Select
        If iSize > 0 Then _
            ActiveSheet.PageSetup.PaperSize = iSize
    End Sub
    
    'Callback for DropDown1 getSelectedItemIndex
    Sub DropDown1_GetSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
        returnedVal = GetPageSize
    End Sub
    
    Function GetPageSize() As String
            Select Case ActiveSheet.PageSetup.PaperSize
            Case xlPaperA3
                GetPageSize = 0 '"A3"
            Case xlPaperA4
                GetPageSize = 1 ' "A4"
            Case xlPaperA5
                GetPageSize = 2 ' "A5"
            End Select
    End Function
    
    ' -- ThisWorkbook
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
       RibUI.InvalidateControl "DropDown1"
    End Sub
    

    enter image description here