Callbacks 2: How to Create Checkboxes and Use the Unique ID

You can have a unique name to all your callbacks if you want for each button. However, sometimes it might make sense to keep the name of the callback the same. This example shows how you can have the same callback name but execute different macros. This can be done because each button has a unique ID and Office knows which button was clicked, which was not possible with Microsoft Word 2003 Toolbars.

Displaying a Unique Supertip

In the previous example, we saw the code for the getSupertip callback:

<button id="getDayofWeek" imageMso="PlayMacro" size="large" label="Day of Week" screentip="Day of Week Formula" getSupertip="getSupertip" onAction="RibbonXOnAction" tag="getDayofWeek" />
<button id="getAge" imageMso="PlayMacro" size="large" label="Age Formula"  screentip="Calculate Age from Date" getSupertip="getSupertip" onAction="RibbonXOnAction" tag="getAge" />
<button id="getExactAge" imageMso="PlayMacro" size="large" label="Exact Age Formula" screentip="Exact Age Formula" getSupertip="getSupertip" onAction="RibbonXOnAction" tag="getExactAge" />

How can we get each button to display a unique supertip? This is accomplished by the Select Case statements. The following code makes use of this statement to get the supertip.

Sub GetSupertip(control As IRibbonControl, ByRef supertip)
    Select Case control.ID
        Case "FooterTabAndPageNumber"
            supertip = "Insert file name and sheet name in left footer and page number in right footer."
        Case "getAge"
            supertip = "Inserts formula in selected cell to calculate age from date in cell to the left of selected cell."
        Case "getExactAge"
            supertip = "Inserts formula in selected cell to calculate exact age (years, months, days) from date in cell to left of selected cell."
        Case "getDayofWeek"
            supertip = "Inserts formula in selected cell to get day of week from date in cell to the left of selected cell."
        Case "FitTo1PageWidth"
            supertip = "Fit content to 1 page width so it prints as 1 page wide while length is not affected."
        Case "AgeCalc"
            supertip = "Enter date in ""Date"" field. Then click here to calculate age and insert into currently selected cell."
        End Select
End Sub

The second line, Select Case control.ID, gives the criteria for fetching the supertip. The supertip is fetched based on the ID of the control by matching the case with the control ID.

This one Sub contains all my supertips, including the three buttons above. This way, I can keep the supertips together in one place. This can be done with any type of callback, not just getSupertip. Another advantage is that if you want to modify your supertips, then you can simply modify the VBA code instead of the XML code, which you will find to be easier than the latter.

Callbacks for Checkboxes

Not all controls have the same callbacks. Depending on type and function, some controls have specific callbacks. The checkBox control, for example, does not allow icons. A "checkbox" replaces the icon. Not only that, but when the Office application is started, it must be determined if the checkbox is to be checked or unchecked. This means that there are more callbacks for a checkbox than just the onAction callback. The following table lists all the callbacks for each control: Controls and their Callbacks.

Callback image

The Print Settings group contains checkboxes for which I have written macros. The functions of the three checkboxes are as follows:

  1. Center Horizontally: When checked, this checkbox will center content horizontally for printing. When unchecked, content will not be centered.
  2. Center Vertically: When checked, this checkbox will center content vertically for printing. When unchecked, content will not be centered.
  3. Fit to 1 Page Width: When checked, this checkbox will fit content to 1 page wide by X pages long. This one is particularly useful when your last column runs off on the second page.

The XML code for these checkboxes are as follows:

<group id="GroupPageSetup" label="Print Settings">
  <checkBox id="CenterHorizontally"  enabled="true" getPressed="GetPressed"
            label="Center Horizontally" screentip="Center Content Horizontally" onAction="PageSetupOnAction" tag="CenterHorizontally" /> 
  <checkBox id="CenterVertically"  enabled="true" getPressed="GetPressed"
            label="Center Vertically" screentip="Center Content Vertically" onAction="PageSetupOnAction" tag="CenterVertically" /> 
  <checkBox id="FitTo1PageWidth"  enabled="true" getPressed="GetPressed"
            label="Fit to 1 Page Width" screentip="Center Content Vertically" onAction="PageSetupOnAction" tag="FitTo1PageWidth" getSupertip="getSupertip" /> 
</group>

Checkboxes have getPressed and onAction callbacks. The getPressed determined whether the checkbox is checked or not when the application is launched. The onAction performs the action when the state of the checkbox is changed, either from checked to unchecked or unchecked to checked, whichever is the case. First, let's look at the getPressed callback.

Dim pressedState As Boolean

Sub GetPressed(control As IRibbonControl, ByRef pressedState)
    Select Case control.ID
    Case "CenterHorizontally"
        If ActiveSheet.PageSetup.CenterHorizontally = True Then
            pressedState = True
        End If
    Case "CenterVertically"
        If ActiveSheet.PageSetup.CenterVertically = True Then
            pressedState = True
        End If
    Case "FitTo1PageWidth"
        If ActiveSheet.PageSetup.FitToPagesWide = 1 Then
            pressedState = True
        End If
    End Select
End Sub

The getPressed call back is set to getPressed="GetPressed" in the XML code. Hence, in VBA editor, I have set my macro to Sub GetPressed(control as IRibbonControl, ByRef pressedState). The ByRef pressedState has Boolean values (True or False) and this is the variable that "calls back" to Office the state of the checkbox. For example, if in Page Setup the checkbox to center horizontally is already checked, then pressedState = True. This variable gets communicated or passed to Office telling it the checkbox is checked. Note that the pressedState variable doesn't change. If it's true on launch then it stays true. Now, the onAction callback, which I have grouped as PageSetupOnAction and used the Select Case statement.

Sub PageSetupOnAction(control As IRibbonControl, pressed As Boolean)
    Select Case control.ID
    Case "CenterHorizontally"
        If pressed = True Then
            ActiveSheet.PageSetup.CenterHorizontally = True
        End If
        If pressed = False Then
            ActiveSheet.PageSetup.CenterHorizontally = False
        End If
    Case "CenterVertically"
        If pressed = True Then
            ActiveSheet.PageSetup.CenterVertically = True
        End If
        If pressed = False Then
            ActiveSheet.PageSetup.CenterVertically = False
        End If
    Case "FitTo1PageWidth"
        If pressed = True Then
            With ActiveSheet.PageSetup
                .FitToPagesWide = 1
                .FitToPagesTall = False
            End With
        End If
        If pressed = False Then
            With ActiveSheet.PageSetup
                .FitToPagesWide = False
                .FitToPagesTall = False
            End With
        End If
    End Select
End Sub

Note:The ByRef variables do not have to be declared as public. Their use is limited to the subroutine. If you want a ByRef variable passed on to another subroutine, you will have to set another publicly-declared variable equal to the ByRef variable. You will see examples of this with checkBoxes and dropDowns in the following pages.

For onAction, the variable we're using is pressed. This variable is also boolean (True or False) but its value changes based on the current state of the checkbox. Hence, the macro that runs is dependent on its checked state. That's why we're using If.. Then statements for the action.

In conclusion, we learned how to use the checkbox control for the RibbonUI along with the Select Case statement in VBA.