Adding A Checkbox Control To Your Ribbon

Checkboxes can be useful controls to toggle on/off specific settings in your add-in. They have a couple of unique properties in comparison to your typical button control, but we’ll explain those nuances in this article.

In this example, I am going to create a single checkbox control on a Ribbon tab called “My Cool Addin”. The checkbox will toggle on/off the spreadsheet gridlines for the ActiveSheet. Because each Excel tab may have a different gridline state (ie gridlines showing or not showing), we will need to analyze each sheet when it is activated and reset the checkbox to align with the current state of the gridlines.

Example File: Ribbon Checkbox Control Example

NOTE: If your checkbox won’t have any instances where the control might need to automatically change on its own, you can ignore any references to the onLoad properties in the example.

– XML Code –

There are three different events to account for while using checkboxes:

• onLoad – This is the macro that stores the RibbonUI object (allows us to refresh the Ribbon anytime we want)
• getPressed – This is the macro that will trigger when the checkbox is loaded/refreshed
• onAction – This is the macro that will trigger whenever the checkbox is clicked

<customUI onLoad="RibbonLoaded_myAddin" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="MyCoolAddin" label="My Cool Addin">
        <group id="GroupA" label="Checkbox Example">
          <checkBox
            id="Checkbox01"
            label="Display Gridlines"
            getPressed="checkbox01_startup"
            onAction="checkbox01_clicked"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

– VBA Code –

Now for the VBA code. First, we will start by creating a macro that will run when the Ribbon UI is initially loaded. This will get our checkbox to be checked/unchecked depending on the current state of the spreadsheet. I’m calling this macro my checkbox01_startup macro since it will run whenever the checkbox is loaded (but you can name it anything you want as long as it matches what was used in your XML code).

Whatever your code does, it should result by providing the returnedVal variable either a true (for checked) or false (for unchecked) value.

Sub checkbox01_startup(control As IRibbonControl, ByRef returnedVal)
'PURPOSE: Set the value of the Checkbox when the Ribbon tab is first activated

'True/False value to send to the Checkbox
  If ActiveWindow.DisplayGridlines = True then
    returnedVal = True
  Else
    returnedVal = False
  End If
End Sub

Next, we will write a macro that will fire when the checkbox is actually clicked by the user. The macro I’m using simply determines if the user checked or unchecked the checkbox control (this value is captured by the pressed variable). If the checkbox is checked (or pressed = True) then I carry out an action. If the checkbox is unchecked then I carry out a separate action.

Sub checkbox01_clicked(control As IRibbonControl, pressed As Boolean)
'PURPOSE:Carryout an action after user clicks the checkbox

Select Case pressed
  Case True
    ActiveWindow.DisplayGridlines = True
  Case False
    ActiveWindow.DisplayGridlines = False
End Select

End Sub

Reloading The Checkbox

As I mentioned near the beginning of this article, my example is using a checkbox that is unique to individual spreadsheets. This means whenever a new spreadsheet tab is selected, I need to reset my checkbox based on if the gridlines are showing or not. For more context on what we will be doing, you may want to visit the article covering How To Refresh The Ribbon On Command.

First, we will need to add this little macro (I recommend adding it to the very top of your VBA code). This VBA macro will essentially store the Ribbon object to a variable that we can call whenever we want upon the initial load of your add-in. Notice how the name of the macro (RibbonLoaded_myAddin) matches the XML code used with the onLoad property in the CustomUI node.

 
Public myRibbon As IRibbonUI 

Sub RibbonLoaded_myAddin(ribbon As IRibbonUI) 
'PURPOSE: Store ribbon instance to a variable upon loading 

Set myRibbon = ribbon 

End Sub 

Next, we will need to code something to trigger a reset of our Checkbox control whenever a new Worksheet is selected. To do this, we will need to trigger a reset of our Checkbox control via the SheetActivate spreadsheet event.

The below code will need to be inserted into the ThisWorkbook code module. You can get to this module by double-clicking the ThisWorkbook object within the Microsoft Excel Objects folder in your VBA Project Pane.

</pre>
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'PURPOSE: Refresh Checkbox after a new sheet is selected

On Error GoTo RestartExcel
  myRibbon.InvalidateControl ("Checkbox01")
On Error GoTo 0

Exit Sub

RestartExcel:
  MsgBox "Please restart Excel for Ribbon UI changes to take effect", _
    vbCritical, "Ribbon UI Refresh Failed"

End Sub