August 26

# A Dynamically Linked Checkbox Matrix with Microsoft Excel

Edit: sorry for some of the squashed images you see in this and other posts. I’m still working on my layout. In the meantime, click on an image to view it in full if you’re having trouble.

Today, I want to show you what I liked to call a “ dynamically linked matrix.” I’ll post an animated gif of the final product at the end of this discussion. I actually started with the gif at the top, but as I began writing, I found the gif nauseating! So if you want to skip ahead to see the final product, scroll down to the bottom of this post.

### Checkbox Matrices

I recently had a project that required the user to toggle a list of features using a matrix similar to the one below. Here, I’ve replaced the grid headings with numbers and letters, but hopefully you can think of a few use-case examples where such a matrix would prove useful.

I like how form control checkboxes can be linked to other cells on a spreadsheet. One advantage of this linkage is that I don’t have to handle each click through the VBA. No code is required to allow the user to toggle between two different states; updating the spreadsheet and checkbox is taken care of for me.

In the matrix above, I wanted each cell to be a clickable area, so even if the checkbox itself was not clicked, I could still capture the same click event if there was a click anywhere in the cell. To achieve this, I created a named range out of the matrix above and called it Main.CheckboxMatrix. Then in Main.CheckboxMatrix, I went through each cell and created a checkbox to be placed directly on top of it. I set the top, left, width, and height values for each checkbox to the same as the cell. I also removed the caption to give the effect above. Here’s the relevant code from the project.

` `

``` For Each rngCurrent In [Main.CheckboxMatrix]    Dim chkbxTemp As CheckBox    Set chkbxTemp = Me.CheckBoxes.Add(1, 1, 1, 1)        itr = itr + 1    Dim rowIndex As Integer    Dim colIndex As Integer        rowIndex = (itr - 1) \ 5 + 1    colIndex = (itr - 1) Mod 5 + 1        With chkbxTemp        .Caption = ""        .Display3DShading = False        .Width = rngCurrent.Width        .Height = rngCurrent.Height        .Left = rngCurrent.Left        .Top = rngCurrent.Top        .Name = "Main.chbx" & itr        .Value = Me.Range("link" & itr)         .OnAction = "Main.RespondToShift"   End WithNext rngCurrent ```

Note: I create the checkbox first with dummy top, left, width, and height values (in this case, I set everything equal to one). I change them later.

To create the effect of “dynamic linking,” I had to go through each checkbox and set a reference to the backend data it represents. I find that making a named reference for each checkbox works best. In my example, each reference is named linkXX where XX is a number. If you look at the code above, I also name each checkbox Main.chbxXX where XX is a number. The numbers at the end of each name allow me to easily associate a checkbox with a link by using a common index.

The following code is found in the worksheet’s change event. When another entry of the dropdown is chosen, I go through link reference to update where it points.

` `

``` For Each curName In ThisWorkbook.Names    Dim strName As String    Dim index As Integer    Dim chkbx As CheckBox        strName = curName.Name    If InStr(1, strName, "link") > 0 Then         index = Replace(curName.NameLocal, "Main!link", "")         Dim rowIndex As Integer        Dim colIndex As Integer                rowIndex = (index - 1) \ 5 + 1        colIndex = (index - 1) Mod 5 + 1         curName.RefersTo = Data.Range("Data." & Target.Value).Cells( _            rowIndex, colIndex)            End IfNext ```

Note that I do no need to reassign the value of each checkbox. Because each checkbox is assign to a link, only updating the links is necessary.

### Conditional Formatting

To help provide visual cues about what items have been toggled, I’ve employed conditional formatting on the cells behind the checkboxes. You can reach these cells by clicking on a cell outside of the matrix and using your arrow keys to traverse your way in. On a much larger matrix, you’d probably want to using the Selection Pane to hide the checkboxes.

The table on the right helps define the conditional formats for each cell (see the image below). In a final project, you’d want to hide this table by hiding its columns or by setting the font and border colors to white (to blend in with the background).

As you might have guessed, the conditional format table is also linked to the backend data. Note that this parallels how the checkboxes are linked.

### Flagging

You might run into a situation where you just want to flag a feature to think about it later. To achieve this, you’ll need to create a third state. Checkboxes will show a check or unchecked boxes for TRUE/1 and FALSE/0 values. However, they will also show a “mixed” state for NA() values.

To flag a feature, the user holds down SHIFT as they click on a checkbox. Using code I’ve borrowed code from Chip Pearson, each checkbox has been assigned the same macro to test if the SHIFT button is held down. That macro is shown below.

` `

``` Public Sub RespondToShi ft()    Dim index As Integer        index = Replace(Application.Caller, "Main.chbx", "")        If IsShiftKeyDown Then        ThisWorkbook.Names("link" & index).RefersToRange.Formula = "=NA()"    End IfEnd Sub ```

Again, following the index numbering convention, we only need to use one macro to handle everything verses using creating a dependent macro for each checkbox. The reason we can do this because the name of the CheckBox is passed into the macro through the Application.Caller variable. If the SHIFT key is held down, we can tell Excel to set the backend data to NA().

One great feature of this example is that all information has been linked. When you toggle a feature, the backend data is changed automatically. Very little VBA is used except to set up the spreadsheet (only performed once, ideally) and to help us relink the different elements together.

Checkbox Matrix Example.xlsm

Tags

## 51: Oz du Soleil & the Global Excel Summit 2021

51: Oz du Soleil & the Global Excel Summit 2021

## Global Excel Summit 2021

Global Excel Summit 2021
• Jordan, I appreciate very much that you’ve linked my blog from those suggested by you in Excel and be you one of my readers.
Iterative calculation is the only solution to manage transitions between states in a game when using only Excel formulas, if one does not trust the macros or are barred by the company.
Your blog is very interesting, I found it thanks to Chandoo and I include among my favorites now.

• […] example, but consider how the dynamic can be used. I use a similar dynamic in a previous article, A Dynamically Linked Checkbox Matrix with Microsoft Excel. I’ve also used similar mechanisms in my new book, Dashboards for […]