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

Required Attributes

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

ATTRIBUTE	WHEN TO USE
id	Create your own dynamicMenu
idMso	Use an existing built-in dynamicMenu
idQ	Create a dynamicMenu shared between namespaces

Required Callback

STATIC ATTRIBUTE	DYNAMIC ATTRIBUTE	ALLOWED VALUES	DEFAULT VALUE	VBA CALLBACK SIGNATURE
(none)	getContent	1 to 4096 characters	(none)	Sub GetContent (control As IRibbonControl, ByRef returnedVal)

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.

INSERT ATTRIBUTE	ALLOWED VALUES	DEFAULT VALUE	WHEN TO USE
insertAfterMso	Valid Mso Group	Insert at end of group	Insert after built-in control
insertBeforeMso	Valid Mso Group	Insert at end of group	Insert before built-in control
insertAfterQ	Valid Group idQ	Insert at end of group	Insert after shared namespace control
insertBeforeQ	Valid Group idQ	Insert at end of group	Insert before shared namespace control

Optional Attributes and Callbacks

STATIC ATTRIBUTE	DYNAMIC ATTRIBUTE	ALLOWED VALUES	DEFAULT VALUE	VBA CALLBACK SIGNATURE FOR DYNAMIC ATTRIBUTE
description	getDescription	1 to 1024 characters	(none)	Sub GetDescription (control As IRibbonControl, ByRef returnedVal)
enabled	getEnabled	true, false, 1, 0	true	Sub GetEnabled (control As IRibbonControl, ByRef returnedVal)
image	getImage	1 to 1024 characters	(none)	Sub GetImage (control As IRibbonControl, ByRef returnedVal)
imageMso	getImage	1 to 1024 characters	(none)	Sub GetImage (control As IRibbonControl, ByRef returnedVal)
itemSize	(none)	normal, large	normal	(none)
keytip	getKeytip	1 to 3 characters	(none)	Sub GetKeytip (control As IRibbonControl, ByRef returnedVal)
label	getLabel	1 to 1024 characters	(none)	Sub GetLabel (control As IRibbonControl, ByRef returnedVal)
screentip	getScreentip	1 to 1024 characters	(none)	Sub GetScreentip (control As IRibbonControl, ByRef returnedVal)
showImage	getShowImage	true, false, 1, 0	true	Sub GetShowImage (control As IRibbonControl, ByRef returnedVal)
showLabel	getShowLabel	true, false, 1, 0	true	Sub GetShowLabel (control As IRibbonControl, ByRef returnedVal)
size	getSize	normal, large	normal	Sub GetSize (control As IRibbonControl, ByRef returnedVal)
supertip	getSupertip	1 to 1024 characters	(none)	Sub GetSupertip (control As IRibbonControl, ByRef returnedVal)
tag	(none)	1 to 1024 characters	(none)	(none)
visible	getVisible	true, false, 1, 0	true	Sub GetVisible (control As IRibbonControl, ByRef returnedVal)

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.