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
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 comboBox |
| idMso | Use an existing built-in comboBox |
| idQ | Create a comboBox 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 |
|---|---|---|---|---|
| (none) | onChange | 1 to 4096 characters | (none) | Sub OnChange (control As IRibbonControl, text As String) |
| 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) |
| maxLength | (none) | 1 to 1024 characters | 1024 | (none) |
| 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) |
| showItemAttribute | (none) | true, false, 1, 0 | true | (none) |
| showItemImage | (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) |
| (none) | getText | 1 to 4096 characters | (none) | Sub GetText (control As IRibbonControl, ByRef returnedVal) |
| visible | getVisible | true, false, 1, 0 | true | Sub GetVisible (control As IRibbonControl, ByRef returnedVal) |
*: 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>
</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 SubCopy 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