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

OaklandJim

Well-known Member
Joined
Nov 29, 2018
Messages
855
Office Version
  1. 365
Platform
  1. Windows
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.

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

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

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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
Here is the code; it worked for me with Excel 2013.

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

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top