Fill in a Custom Ribbon ComboBox (Runtime)

Question

I have a combobox in a custom Ribbon created with CustomEditor (XML).

I would like to modify combobox elements when I press a command button located in a Excel Sheet.

            <comboBox id="Combo3" getItemCount="Módulo4.Número_Elementos_Combobox" getItemID="Módulo4.Identificador_Items" getItemLabel="Módulo4.Texto_Items" image="Foto_Excel" onChange="Módulo1.Todos_Los_Combos">
            </comboBox>

Many thanks in advance. Regards. José.

Answer 1

You'll need to use the InvalidateControl method of the Ribbon object in order to invalidate the cache, and update the combobox.

First, if you haven't already done so, you'll need to include an onLoad paramater for your customUI tag in your RibbonX code. This paramater will specify the VBA callback procedure to be executed when the workbook is opened, so that the ribbon object can be created.

Then, you'll need to include the VBA callback procedures for getItemCount, getItemLabel, and getItemID. When the combobox is invalidated, getItemCount gets called. Then, in turn, getItemLabel and getItemID get called.

The getItemCount procedure is where you specify how many items you want to include in your combobox. As a result, both getItemLabel and getItemID each get called the same number of times so that the ID's and labels can be updated accordingly.

Then, of course, you'll need include the procedure that will be assigned to your button, which will actually invalidate the combobox.

Since you haven't provided all of the relevant details, here's an example that hopefully you'll be able to adapt for your needs.

RibbonX Code

<!--RibbonX Visual Designer 2.33 for Microsoft Excel CustomUI14 . XML Code produced on 2020/05/16-->
<customUI 
    xmlns="http://schemas.microsoft.com/office/2009/07/customui"
    onLoad="Initialize">
    <ribbon >
        <tabs >
            <tab 
                id="Tab1"
                label="Tab1">
                <group 
                    id="Group1"
                    label="Group1">
                    <comboBox 
                        id="Combo3"
                        label="MyCombobox"
                        getItemCount="Combo3_getItemCount"
                        getItemID="Combo3_getItemID"
                        getItemLabel="Combo3_getItemLabel"
                        getText="Combo3_getText"
                        onChange="Combo3_onChange"/>
                </group >
            </tab >
        </tabs >
    </ribbon >
</customUI >

VBA Code

Option Explicit

Dim myRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub Initialize(ribbon As IRibbonUI)
    Set myRibbon = ribbon
End Sub

'Callback for Combo3 getItemCount (called once when the combobox is invalidated)
Sub Combo3_getItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 10 'the number of items for combobox
End Sub

'Callback for Combo3 getItemID (called 10 times when combobox is invalidated)
Public Sub Combo3_getItemID(control As IRibbonControl, index As Integer, ByRef id)
    id = "ComboboxItem" & index + 1
End Sub

'Callback for Combo3 getItemLabel (called 10 times when combobox is invalidated)
Sub Combo3_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = "Item" & index + 1
End Sub

'Callback for Combo3 getText
Sub Combo3_getText(control As IRibbonControl, ByRef returnedVal)
    returnedVal = "" 'clears the text from the combobox
End Sub

'Callback for Combo3 onChange
Sub Combo3_onChange(control As IRibbonControl, text As String)
    MsgBox "You have chosen " & text
End Sub

Sub UpdateCombo3()
    myRibbon.InvalidateControl "Combo3" 'invalidates the cache for the combobox
End Sub