Programming with Excel VBA
Home » learn » Programming with Excel VBA » dropDown Element

dropDown Element

16 minutes to read
22 0

The dropDown element specifies a drop-down control that allows users to make a selection from a list of options. A drop-down control can optionally have buttons after its selection items.

Graphical View of dropDown Attributes

The following figure shows all the visible graphical attributes you can set on the dropDown control.

This is specified using the following XML:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
          onLoad="IRibbonUI_onLoad">
	<!--true: show only your tab in Excel-->
	<ribbon startFromScratch="true">
		<tabs>
			<tab id="customTab"
			     label="Custom Tab">
				<group id="customGroup"
				       label="Custom Group">
					<dropDown id="ddSelect"
					          label="Choose: "
					          imageMso="HappyFace"
					          keytip="K"
					          screentip="This is the screentip string"
					          supertip="This is the supertip string"
					          onAction="ddSelect_onAction">
						<item id="ddSelect_1"
						      label="Hello World"/>
						<item id="ddSelect_2"
						      label="Hello Excel"/>
						<item id="ddSelect_3"
						      label="Hello ExcelBaby"/>
					</dropDown>
					<dropDown id="ddSelectDisable"
					          label="Apply To:"
					          enabled="false"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

The first dropDown list does have a </dropDown> tag to close it. This is because it holds a static list of item objects declared directly in the XML code.

The second dropDown list does not require any static child objects declared in the XML, and can be closed by using /> at the end.

Parent Elements

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 dropDown
idMso Use an existing built-in dropDown
idQ Create a dropDown shared between namespaces

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
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)
(none) getItemCount 1 to 1024 characters (none) Sub GetItemCount (control As IRibbonControl, ByRef returnedVal)
(none) getItemID 1 to 1024 characters (none) Sub GetItemID (control As IRibbonControl, index As Integer, ByRef id)
(none) getItemImage Unique text string (none) Sub GetItemImage (control As IRibbonControl, index As Integer, ByRef returnedVal)
(none) getItemLabel 1 to 1024 characters (none) Sub GetItemLabel (control As IRibbonControl, index As Integer, ByRef returnedVal)
(none) GetItemScreenTip 1 to 1024 characters (none) Sub GetItemScreenTip (control As IRibbonControl, index As Integer, ByRef returnedVal)
(none) getItemSupertip 1 to 1024 characters (none) Sub GetItemSuperTip (control As IRibbonControl, index As Integer, ByRef returnedVal)
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)
(none) getSelectedItemID Unique text string (none) Sub GetSelectedItemID (control As IRibbonControl, ByRef returnedVal)
(none) getSelectedItemIndex 1 to 1024 (none) Sub GetSelectedItemIndex (control As IRibbonControl, ByRef returnedVal)
showImage getShowImage true, false, 1, 0 true Sub GetShowImage (control As IRibbonControl, ByRef returnedVal)
showItemImage (none) true, false, 1, 0 true (none)
showItemLabel (none) true, false, 1, 0 true (none)
showLabel getShowLabel true, false, 1, 0 true Sub GetShowLabel (control As IRibbonControl, ByRef returnedVal)
sizeString (none) 1 to 1024 characters 12* (none)
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)
(none) onAction 1 to 1024 characters (none) Sub OnAction (control As IRibbonControl, selectedId As String, selectedIndex As Integer)

*: The default value for the sizeString attribute (if the attribute is not declared at all) is approximately 12, but this varies depending on the characters used and the system font.

Using Built-in dropDown Controls

Excel has only one native dropDown control: the BorderStyle control. The following example adds the built-in BorderStyle control to a custom group.

This is specified using the following XML:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<!-- true: show only your tab in Excel -->
	<ribbon startFromScratch="true">
		<tabs>
			<tab id="customTab"
			     label="Custom Tab">
				<group id="customGroup"
				       label="Custom Group">
					<dropDown idMso="BorderStyle"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Because we are only using built-in controls, this example does not require VBA.

Creating Custom dropDown Controls

The following example creates two dropDown elements in tandem.

The first control lists all sheets in the workbook and updates the dropdown list when sheets are added or removed.

The second control allows us to toggle the visibility of the selected sheet between three states of Excel: xlSheetVisible, xlSheetHidden, and xlSheetVeryHidden.

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">
	<!--true: show only your tab in Excel-->
	<ribbon startFromScratch="true">
		<tabs>
			<tab id="customTab"
			     label="Custom Tab">
				<group id="customGroup"
				       label="Custom Group">
					<dropDown id="ddSelectSheet"
					          label="Apply To:"
					          getItemID="ddSelectSheet_getItemID"
					          getItemCount="ddSelectSheet_getItemCount"
					          getItemLabel="ddSelectSheet_getItemLabel"
					          onAction="ddSelectSheet_onAction"/>
					<dropDown id="ddSheetVisible"
					          label="Set To:"
					          getEnabled="ddSheetVisible_getEnabled"
					          getSelectedItemID="ddSheetVisible_getSelectedItemID"
					          onAction="ddSheetVisible_onAction">
						<item id="ddSheetVisible_1"
						      label="Visible"/>
						<item id="ddSheetVisible_2"
						      label="Hidden"/>
						<item id="ddSheetVisible_3"
						      label="VeryHidden"/>
					</dropDown>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

VBA

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

'------------------ Modules ------------------
'--------------- ExcelBaby.com ---------------

Option Explicit

Public MyRibbon As IRibbonUI
Dim sSheetName As String    'Store the sheet name that was selected
Private vCurrentSelectSheetID As Variant
Private vCurrentSheetVisibleID As Variant

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

'Callback for ddSelectSheet getItemID
Sub ddSelectSheet_getItemID(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = "ddSelectSheet" & index
End Sub

'Callback for rxddSelectSheet getItemCount
Sub ddSelectSheet_getItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = Worksheets.Count  'Tells the control how many items exist
End Sub

'Callback for ddSelectSheet getItemLabel
Sub ddSelectSheet_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = Worksheets(index + 1).Name    'index starts at 0, retrun sheet name
End Sub

'Callback for ddSelectSheet onAction
Sub ddSelectSheet_onAction(control As IRibbonControl, id As String, index As Integer)
    sSheetName = Worksheets(index + 1).Name
    vCurrentSelectSheetID = id
    
    vCurrentSheetVisibleID = Empty  'Reset callback so ddSheetVisible control can be selected again.
    MyRibbon.InvalidateControl ("ddSheetVisible")   'Invalidates the cache of ddSheetVisible control
End Sub

'Callback for ddSheetVisible getEnabled
Sub ddSheetVisible_getEnabled(control As IRibbonControl, ByRef returnedVal)
    If IsEmpty(vCurrentSelectSheetID) Then  'Check that a sheet has been selected
        returnedVal = False 'Disable control
    Else
        returnedVal = True  'Enable control
    End If
End Sub

'Callback for ddSheetVisible getSelectedItemID
Sub ddSheetVisible_getSelectedItemID(control As IRibbonControl, ByRef returnedVal)
    returnedVal = vCurrentSheetVisibleID
End Sub

'Callback for ddSheetVisible onAction
Sub ddSheetVisible_onAction(control As IRibbonControl, id As String, index As Integer)
    On Error Resume Next
    Select Case id
        Case "ddSheetVisible_1"
            Worksheets(sSheetName).Visible = xlSheetVisible
        Case "ddSheetVisible_2"
            Worksheets(sSheetName).Visible = xlSheetHidden
        Case "ddSheetVisible_3"
            Worksheets(sSheetName).Visible = xlSheetVeryHidden
    End Select
    
    vCurrentSheetVisibleID = Empty  'Reset callback so ddSheetVisible control can be selected again.
    MyRibbon.InvalidateControl ("ddSheetVisible")   'Invalidates the cache of ddSheetVisible control
    
    Select Case Err.Number
        Case 9  'If the user deletes the select sheet, tell the user to reselect
            MsgBox "Sorry, this sheet does not exist, please select again!"
        Case 1004   'Tell user if it is last visible sheet
            MsgBox "Sorry, this is the only visible sheet." & vbCrLf & "You can't hide them all!"
    End Select

End Sub

Copy and paste the following VBA code into ThisWorkbook:

'------------------ ThisWorkbook ------------------
Option Explicit

Private Sub Workbook_SheetActivate(ByVal sh As Object)
    'Invalidate the tab each time a worksheet is activated
    'Redraw controls after adding or deleting sheets
    MyRibbon.Invalidate
End Sub

Download Custom dropDown Demo

Download Custom dropDown Demo.

Refresh Custom Ribbon Dropdown box after callback is executed

Why do you need to reset Dropdown box? If you select the same dropdown option again that was previously selected, the callback will not work. You always need to select a different option than was selected before. I suggest you try Combobox element, you can try getText callback to set empty value for Combobox.

If you really need the dropdown box, you can set Dropdown box with empty value (not 0) and redraw the control on each onAction callback. For setting value for Dropdown box, we could try getSelectedItemID callback or getSelectedItemIndex callback.

The following example creates a dropDown element that enables the user to run three different macros. In this example, you can select the same dropdown option you selected earlier and the callback will still work.

XML

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
          onLoad="IRibbonUI_onLoad">
	<!--true: show only your tab in Excel-->
	<ribbon startFromScratch="true">
		<tabs>
			<tab id="customTab"
			     label="Custom Tab">
				<group id="customGroup"
				       label="Custom Group">
					<dropDown id="ddSelect"
					          label="Choose: "
					          getSelectedItemID="ddSelect_getSelectedItemID"
					          onAction="ddSelect_onAction">
						<item id="ddSelect_1"
						      label="Hello World"/>
						<item id="ddSelect_2"
						      label="Hello Excel"/>
						<item id="ddSelect_3"
						      label="Hello ExcelBaby"/>
					</dropDown>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

VBA

'------------------ Modules ------------------
'--------------- ExcelBaby.com ---------------

Option Explicit

Public MyRibbon As IRibbonUI

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

'Callback for ddSelect getSelectedItemID
Sub ddSelect_getSelectedItemID(control As IRibbonControl, ByRef returnedVal)
    returnedVal = Empty
End Sub

'Callback for ddSelect onAction
Sub ddSelect_onAction(control As IRibbonControl, id As String, index As Integer)

    Select Case id
        Case "ddSelect_1"
            MsgBox "Hello World"
        Case "ddSelect_2"
            MsgBox "Hello Excel"
        Case "ddSelect_3"
            MsgBox "Hello ExcelBaby.com"
    End Select

    MyRibbon.InvalidateControl ("ddSelect")   'Invalidates the cache of a single control
End Sub

Or dynamically set the value of getSelectedItemID callback:

'------------------ Modules ------------------
'--------------- ExcelBaby.com ---------------

Option Explicit

Public MyRibbon As IRibbonUI
Private vCurrentSelectID As Variant

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

'Callback for ddSelect getSelectedItemID
Sub ddSelect_getSelectedItemID(control As IRibbonControl, ByRef returnedVal)
    returnedVal = vCurrentSelectID
End Sub

'Callback for ddSelect onAction
Sub ddSelect_onAction(control As IRibbonControl, id As String, index As Integer)

    Select Case id
        Case "ddSelect_1"
            MsgBox "Hello World"
        Case "ddSelect_2"
            MsgBox "Hello Excel"
        Case "ddSelect_3"
            MsgBox "Hello ExcelBaby.com"
    End Select
    
    vCurrentSelectID = Empty
    MyRibbon.InvalidateControl ("ddSelect")   'Invalidates the cache of a single control
End Sub

Download Reset dropDown Demo

Download Reset dropDown Demo.

❮ PreviousNext ❯

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>