dynamicMenu Element

The dynamicMenu element specifies a dynamic menu control that populates its contents dynamically.

Graphical View of dynamicMenu Attributes

The following example shows the visible graphical attributes you can set on the dynamicMenu control.

This is specified using the following XML:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon>
		<tabs>
			<tab id="tabLearn"
			     label="Learn"
			     insertBeforeMso="TabHome">
				<group id="grpDemo"
				       label="Demo">
					<dynamicMenu id="dynamic"
					             label="Dynamic Menu"
					             imageMso="HappyFace"
					             size="normal"
					             keytip="D"
					             getContent="GetMenuContent"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

The GetMenuContent callback function is called when the menu is dropped, and in this case would return a string with the following VBA:

''------------------ Modules ------------------
''--------------- ExcelBaby.com ---------------
Option Explicit

'Callback for dynamic getContent
Sub GetMenuContent(control As IRibbonControl, ByRef returnedVal)
    returnedVal = "<menu xmlns=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
        "<button id=""button1"" label=""Button 1"" />" & _
        "<button id=""button2"" label=""Button 2"" />" & _
        "<button id=""button3"" label=""Button 3"" />" & _
        "</menu>"
End Sub

Parent Elements

• box
• buttonGroup
• dynamicMenu
• group
• menu
• officeMenu

Children Elements

• button
• checkbox
• control
• dynamicMenu
• gallery
• menu
• menuSeparator
• splitButton
• toggleButton

Required Attributes

The id, idQ, and idMso attributes are mutually exclusive. At least one of these attributes MUST be specified.

Required Callback

Optional insert Attributes

The insertAfterMso, insertAfterQ, insertBeforeMso, and insertBeforeQ attributes are mutually exclusive. If none of these attributes are specified, the controls SHOULD be appended to the existing set of controls, in the order they are defined in the XML.

Optional Attributes and Callbacks

Using Built-in dynamicMenu Controls

Excel doesn't have a built-in dynamicMenu element.

Creating Custom dynamicMenu Controls

The following example adds a dynamic menu that creates a new file based on your existing templates in your personal templates folder and can be refreshed at any time.

If no Excel template is found:

If you add a template in Personal Templates Folder(C:\Users\YourUserName\AppData\Roaming\Microsoft\Templates\) , the new template will be displayed in the menu bar after clicking the Refresh Templates  button:

XML

Copy and paste the following XML code into customUI14.xml. If you don't know how to do it, read this article: Creating a Custom Ribbon.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
          onLoad="IRibbonUI_onLoad">
	<ribbon>
		<tabs>
			<tab id="tabLearn"
			     label="Learn"
			     insertBeforeMso="TabHome">
				<group id="grpNewFile"
				       label="New File">
					<dynamicMenu id="dmnuTemplates"
					             label="Create from..."
					             imageMso="CreateReportFromWizard"
					             size="large"
					             getContent="dmnuTemplates_getContent"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

VBA

Copy and paste the following VBA code into a normal module:

Option Explicit
''------------------ Modules ------------------
''--------------- ExcelBaby.com ---------------
Dim MyRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub IRibbonUI_onLoad(ribbon As IRibbonUI)
    Set MyRibbon = ribbon
End Sub

'Callback for dmnuTemplates getContent
Sub dmnuTemplates_GetContent(control As IRibbonControl, ByRef returnedVal)
    'Callback for GetContent to return XML used to create dynamicMenu
    Dim objFSO As Object
    Dim objTemplateFolder As Object
    Dim file As Object
    Dim sXML As String
    Dim btnCount As Long

    'Open the XML string
    sXML = "<menu xmlns=""http://schemas.microsoft.com/office/2009/07/customui"">"

    'Create FSO object and set to templates folder
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTemplateFolder = objFSO.getfolder(Application.TemplatesPath)

    'Add template files
    If objTemplateFolder.Files.Count > 0 Then
        For Each file In objTemplateFolder.Files
            'Check if file is a temporary file
            If Not Left(file.Name, 2) = "~$" Then
                'File is not a temp file, so check extension
                Select Case LCase(Right(file.Name, 4))
                    Case ".xlt", "xltx", "xltm"
                        'Excel template.
                        sXML = sXML & _
                            "<button id=""btnDyna" & btnCount & """ " & _
                            "label=""" & file.Name & """ " & _
                            "imageMso=""FileSaveAsExcelXlsx"" " & _
                            "tag=""" & file.Path & """ " & _
                            "onAction=""btnDyna_onAction""/>" & vbCrLf
                        btnCount = btnCount + 1
                    Case Else
                        'Unknown format. Ignore.
                End Select
            End If
        Next file
    End If
    
    'Release the FSO objects
    Set file = Nothing
    Set objTemplateFolder = Nothing
    Set objFSO = Nothing

    'Create a "No Excel Templates Found" button if not
    If btnCount = 0 Then
        sXML = sXML & "<button id=""btnDyna0"" label=""No Excel Templates Found"" imageMso=""FileFind"" />"
    End If
    
    'Create a menu separator
    sXML = sXML & "<menuSeparator id=""separator""/>"
    
    'Add Refresh button
    sXML = sXML & _
        "<button id=""btnRefresh"" " & _
        "label=""Refresh Templates"" " & _
        "imageMso=""RecurrenceEdit"" " & _
        "onAction=""btnDyna_onAction""/>"
        
    'Close the menu tags
    sXML = sXML & "</menu>"
    
    returnedVal = sXML
End Sub

'Callback for btnDyna onAction
Sub btnDyna_onAction(control As IRibbonControl)
    If control.ID = "btnRefresh" Then
        MyRibbon.InvalidateControl ("dmnuTemplates")
    Else
        Workbooks.Add (control.Tag)
    End If
End Sub

Download Custom dynamicMenu Demo

Download dynamicMenu Element Demo.