Ribbon Customization: Filling Dropdown Lists

In the previous post, I showed you how to create a simple customization to close the currently open object. Continuing along the lines of adding object helpers to an application, we'll add two dropdown lists to a customization to work with forms. The first will list all forms in a database and the second will list the open forms in an application. Start with the XML for the customization. For readability, I've left out the button created in the previous post.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="tab1" label="Object Helpers">
                <group id="grp1" label="Helpers">
                    <dropDown id="ddlAllForms"
                              label="All Forms"
                              imageMso="CreateFormMoreFormsGallery"
                              sizeString="AAAAAAAAAAAAAAA"
                              getItemCount="OnGetItemCount"
                              getItemLabel="OnGetItemLabel"
                              onAction="OnSelectItem">
                    </dropDown>
                    <dropDown id="ddlOpenForms"
                              label="Open Forms"
                              imageMso="CreateFormMoreFormsGallery"
                              sizeString="AAAAAAAAAAAAAAA"
                              getItemCount="OnGetItemCount"
                              getItemLabel="OnGetItemLabel"
                              onAction="OnSelectItem">
                    </dropDown>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Add this customization as an entry in a USysRibbons table in the database and set the Ribbon Name property of the database.

The customization shown here defines three callback routines used by the dropdown: getItemCount, getItemLabel, and onAction. These callbacks are used to determine the number of items in a dropdown, the label for a single item, and when an item is selected respectively. We're going to write one callback to cover both controls, starting with OnGetItemCount. We'll tell the dropdown to either display the number of forms in the database using the AllForms collection, or the number of open forms using the Forms collection.

Public Sub OnGetItemCount(ctl As IRibbonControl, ByRef Count)
    ' set the number of items to the number of forms in the database
    If (ctl.ID = "ddlAllForms") Then
        Count = CurrentProject.AllForms.Count  ' Total number of forms
    ElseIf (ctl.ID = "ddlOpenForms") Then
        Count = Forms.Count                    ' Number of open forms
    End If
End Sub

Next, create the OnGetItemLabel callback:

Public Sub OnGetItemLabel(ctl As IRibbonControl, Index As Integer, ByRef Label)
    ' set the label
    If (ctl.ID = "ddlAllForms") Then
        Label = CurrentProject.AllForms(Index).Name
    ElseIf (ctl.ID = "ddlOpenForms") Then
        ' for open forms, use the Caption property of the form if set
        If (Len(Forms(Index).Caption) > 0) Then
            Label = Forms(Index).Caption
        Else
            Label = Forms(Index).Name
        End If
    End If
End Sub

Now, add the OnSelectItem callback that is called when you choose an item in the dropdown. Here, we'll open the currently selected form.

Public Sub OnSelectItem(ctl As IRibbonControl, selectedId As String, selectedIndex As Integer)
    If (ctl.ID = "ddlAllForms") Then
        DoCmd.OpenForm CurrentProject.AllForms(selectedIndex).Name
    ElseIf (ctl.ID = "ddlOpenForms") Then
        DoCmd.OpenForm Forms(selectedIndex).Name
    End If
End Sub

Re-open the database to try it out and open some forms. When you go to the Object Helpers tab, you should have something that looks like this: