Ribbon: Need nudges to understand/implement checkbox & dropdown controls

[OaklandJim]

Team Ribbon

I have read suggested materials from de Bruin, Pope, etc. I keep learning parts of the story. I figger that I'm 30% there. But so much to understand... Right now trying to be more familiar with the callback stuff. It is all somewhat confusing, to me at least.

First, based on examples I am creating a file named Excel.officeUI in dir C:\Users\Jim\AppData\Local\Microsoft\Office\.

I read about another way to do implement the ribbon using customUI.xml (=xl 2007?) and customUI14.xml (>xl2010). I'm not sure what to do with those files. Of course generating the xml is the first step and not very difficult but what next? Is there a resource that describes implementation of these.

My approach seems to work sluggishly. After I run code that creates the Excel.officeUI I have to touch the VBA editor before the ribbon shows up. Even if I use the example clear ribbon code (that does return file to it's bare bones state as shown just below) the ribbon is still visible. I suppose that closing and clearing the file then reopening the file will work.

<mso:customUI xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui"><mso:ribbon></mso:ribbon></mso:customUI>

Eventually I'm hoping for event code that shows or removes the custom ribbon tab when changing worksheets.

Currently I am having difficulty understanding BASIC chekboxes and dropdowns. I generated the code below but nothing happens when I click the controls on the ribbon. Screentips work. I can check/uncheck the chekbox but cannot get any vba routines to fire. No items are shown in the dropdown and no code fires.

<mso:customUI xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>
  <mso:ribbon>
   <mso:qat/>
    <mso:tabs>
      <mso:tab id='tab1' label='Test Ribbon' insertBeforeQ='mso:TabFormat'>

         <mso:group id='group4' label='Toggle Visible' autoScale='true'>
           <mso:checkBox id='G4Checkbox1' label='Dates' onAction='G4_Checkbox1_Selected' getPressed='G4_Checkbox1_Pressed' screentip='Hide dates column.'/>
        </mso:group>

         <mso:group id='group5' label='Select Color' autoScale='true'>
           <mso:dropDown id='G5Dropdown1' label='Select Primary' onAction='G5_Dropdown1_Selected' screentip='Select the preferred primary color.'/>
             <mso:item id='G5Item1' label='Blue'/>
             <mso:item id='G5Item2' label='Red'/>
             <mso:item id='G5Item3' label='Green'/>
        </mso:group>

      </mso:tab>
    </mso:tabs>
  </mso:ribbon>
</mso:customUI>

I do have the subs named in the controls' definitions but they do not fire at all. I bet that I missed something fundamental.

Public Function G4_Checkbox1_Selected(pControl As IRibbonControl, ByRef pbPressedItem As Boolean)
    MsgBox "Checkbox G4 1 toggled, pbPressedItem = " & pbPressedItem
End Function

Public Function G4_Checkbox1_Pressed(pControl As IRibbonControl, ByRef pvPressedItem As Variant)
    MsgBox "Checkbox G4 1 pressed, pvPressedItem = " & pvPressedItem
End Function

Public Function G5_Dropdown1_Selected( _
   ByRef control As Office.IRibbonControl, _
   ByRef dropdownID As String, _
   ByRef selectedIndex As Variant)
    
    MsgBox "Dropdown G5-1 selected item, selectedIndex = " & selectedIndex
End Function

Public Function G5_Item1()
    MsgBox "ItemG5_1 selected"
End Function

Public Function G5_Item2()
    MsgBox "ItemG5_2 selected"
End Function

Public Function G5_Item3()
    MsgBox "ItemG5_3 selected"
End Function

 

[Worf]

The workbook below has a working dropdown example; see if you can test it successfully with Excel 2019.

I usually modify my XML using the Custom UI Editor.

Ribbon dropdown

 

[Worf]

Here is the code; it worked for me with Excel 2013.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
    <tabs>
      <tab id="myTab" label="MyTab">
        <group id="myGroupDD" label="Has Dropdown">
          <dropDown id="dd1"
        label="Dropdown"
        getItemCount="DDItemCount"
        getItemLabel="DDListItem"
        onAction="DDOnAction"     
        getSelectedItemIndex="DDItemSelectedIndex"/>

         <button id="SelectedItem"
        label="Value Selected Item"
        onAction="ValueSelectedItem"
        size="large"
        imageMso="HappyFace" />

        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Dim ItemCount As Integer, ListItemsRg As Range, MySelectedItem$
' by Ron de Bruin
''=========Drop Down Code =========

''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.
Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
    With Sheet1.Range("A7:A100")
        Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
        ItemCount = ListItemsRg.Rows.Count
        returnedVal = ItemCount
    End With
End Sub

''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
Sub DDListItem(control As IRibbonControl, index%, ByRef returnedVal)
    returnedVal = ListItemsRg.Cells(index + 1)
    ''index is 0-based, our list is 1-based so we add 1.
End Sub

''Drop down change handler.
''Called when a drop down item is selected.
Sub DDOnAction(control As IRibbonControl, ID As String, index%)
' Two ways to set the variable MySelectedItem to the dropdown value

'way 1
    MySelectedItem = ListItemsRg.Cells(index + 1)

    ''way 2
    'Call DDListItem(control, index, MySelectedItem)
End Sub

''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1)
End Sub

''------- End DD Code --------

''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem(control As IRibbonControl)
    MsgBox "The variable MySelectedItem have the value = " & MySelectedItem & vbNewLine & _
           "You can use MySelectedItem in other code now to use the dropdown value"
End Sub

 

[OaklandJim]

Thanks for the nudge. I think I've got it working now!