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.
There are three different events to account for while using checkboxes:
<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>
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
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