comboBox Element

The comboBox element specifies a standard combo box control that allows a user to input a text string or select one from a list.

Graphical View of comboBox Attributes

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

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">
					<comboBox id="cbSelectSheet"
					          label="Activate: "
					          image="custom-icon"
					          keytip="K"
					          screentip="This is the screentip string"
					          supertip="This is the supertip string">
						<item id="itemSheet1"
						      label="Sheet1"/>
						<item id="itemSheet2"
						      label="Sheet2"/>
						<item id="itemSheet3"
						      label="Sheet3"/>
					</comboBox>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Parent Elements

• box
• group

Children Elements

• item

Required Attributes

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

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

*: 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 comboBox Controls

The following example adds two built-in comboBoxes (Font and FontSize) 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">
					<comboBox idMso="Font"/>
					<comboBox idMso="FontSize"/>
				</group&gt;
			</tab>
		</tabs>
	</ribbon>
</customUI>

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

Creating Custom comboBox Controls

The following example creats a comboBox that enables the user to activate any sheet in Excel. If the target sheet is hidden, you will be asked if you want to show it. If you select "No", the program will exit, if you select "Yes", the program will unhide and activate the target sheet.

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">
					<comboBox id="cbSelectSheet"
					          label="Activate: "
					          getItemCount="cbItemCount"
					          getItemID="cbItemID"
					          getItemLabel="cbItemLabel"
					          getText="cbItemText"
					          onChange="cbOnChange"/>
				</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

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

'Callback for cbSelectSheet getItemCount
Sub cbItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = ActiveWorkbook.Sheets.Count
End Sub

'Callback for cbSelectSheet getItemID
Sub cbItemID(control As IRibbonControl, index As Integer, ByRef returnedVal)
'    returnedVal = ThisWorkbook.Worksheets(index + 1).CodeName
End Sub

'Callback for cbSelectSheet getItemLabel
Sub cbItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = ThisWorkbook.Worksheets(index + 1).Name
End Sub

'Callback for cbSelectSheet getText
Sub cbItemText(control As IRibbonControl, ByRef returnedVal)
    returnedVal = ActiveSheet.Name
End Sub

'Callback for cbSelectSheet onChange
Sub cbOnChange(control As IRibbonControl, text As String)
    On Error Resume Next

    If Worksheets(text).Visible = xlSheetHidden Then
        Dim Response As Integer
        Response = MsgBox("The worksheet is hideen, do you want to unhide it?", vbYesNo)
        If Response = vbYes Then
            Worksheets(text).Visible = xlSheetVisible
            Worksheets(text).Activate
        Else
            MyRibbon.InvalidateControl ("cbSelectSheet") 'Invalidates the cache
            Exit Sub
        End If
    End If
    Worksheets(text).Activate
End Sub

Copy and paste the following VBA code into ThisWorkbook:

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

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    'Invalidate the controls each time a worksheet is activated
    MyRibbon.InvalidateControl ("cbSelectSheet") ' Invalidates the cache of a single control
End Sub

Download

Download Custom comboBox Demo.