checkBox Element

The checkBox element enables users to toggle between two states.

Graphical View of checkBox Attributes

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

This is specified using the following XML:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon startFromScratch="true">
		<tabs>
			<tab id="customTab"
			     label="Custom Tab">
				<group id="customGroup"
				       label="Custom Group">
					<checkBox id="mycheckbox"
					          label="Check Box"
					          keytip="C"
					          screentip="This is the screentip string"
					          supertip="This is the supertip string"
					          onAction="CheckBox_OnAction"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Parent Elements

• box
• dynamicMenu
• group
• menu
• officeMenu (Note that when the checkbox is unchecked, only the description, but not the checkbox, appears on the menu.)

Children Elements

The checkBox control does not support child objects of any kind.

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

Required Callback

DYNAMIC ATTRIBUTE	ALLOWED VALUES	VBA CALLBACK SIGNATURE
onAction	1 to 4096 characters	Sub OnAction (control As IRibbonControl, pressed as Boolean)

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 4096 characters	(none)	Sub GetDescription (control As IRibbonControl, ByRef returnedVal)
enabled	getEnabled	true, false, 1, 0	true	Sub GetEnabled (control As IRibbonControl, 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)
(none)	getPressed	true, false, 1, 0	false	Sub GetPressed (control As IRibbonControl, ByRef returnedVal)
screentip	getScreentip	1 to 1024 characters	(none)	Sub GetScreentip (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 checkBox Controls

Because you are only using built-in controls, and do not need to program any callback macros.

The following XML creates a clone of the control with the identifier of "ViewRulerExcel", "GridlinesExcel", "ViewHeadings", "ViewFormulaBar".

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon startFromScratch="true">
		<tabs>
			<tab id="customTab"
			     label="Custom Tab">
				<group id="customGroup"
				       label="Custom Group">
					<checkBox idMso="ViewRulerExcel"/>
					<checkBox idMso="GridlinesExcel"/>
					<checkBox idMso="ViewHeadings"/>
					<checkBox idMso="ViewFormulaBar"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Where can I find the full list of idMso?

idMSO Full List in Excel for Windows

idMSO Full List in Excel for Mac

Creating Custom checkBox Controls

The following example creats a checkBox that enables the user to toggle between R1C1 and A1 Reference Style.

XML

Copy and paste the following XML code into customUI14.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">
					<checkBox id="chkR1C1"
					          label="R1C1 Formulas"
					          keytip="R"
					          screentip="R1C1 Formulas"
					          supertip="Change the way Excel formulas refer to cells."
					          getPressed="chkR1C1_getPressed"
					          onAction="chkR1C1_click"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

VBA

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

Option Explicit

Public MyRibbon As IRibbonUI

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

'Callback for chkR1C1 getPressed
Sub chkR1C1_getPressed(control As IRibbonControl, ByRef returnedVal)
    If Application.ReferenceStyle = xlR1C1 Then returnedVal = True
End Sub

'Callback for chkR1C1 onAction
Sub chkR1C1_click(control As IRibbonControl, pressed As Boolean)
    Select Case pressed
        Case True
            Application.ReferenceStyle = xlR1C1
        Case False
            Application.ReferenceStyle = xlA1
    End Select
End Sub

When the R1C1 Formulas checkbox is unchecked:

When the R1C1 Formulas checkbox is checked:

Unfortunately, there is an issue remaining with this checkBox: What if someone goes through the Excel File > Options > Formulas > Working with formulas > R1C1 reference style to change the value of that checkBox? In that case, the checkBox will not be updated.

While you can’t make this perfectly transparent, you can force it to update whenever you activate a new worksheet.

To do this, you can use InvalidateControl method to invalidate the cached value for a single control — in this case, the chkR1C1 checkBox.

Copy and paste the following VBA code into ThisWorkbook:

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

Download

Download Custom checkBox Demo.