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.

Required Callback

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

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.