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.

image

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 With
Next 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.

Dynamic Linking

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 If
Next

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. 

image

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).

image

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. 

image

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 RespondToShift()
    Dim index As Integer
   
    index = Replace(Application.Caller, "Main.chbx", "")
   
    If IsShiftKeyDown Then
        ThisWorkbook.Names("link" & index).RefersToRange.Formula = "=NA()"
    End If
End 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().

What I like about dynamic linking

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.

Dynamic Links

Get the download file

Checkbox Matrix Example.xlsm

Advertisements

4 thoughts on “A Dynamically Linked Checkbox Matrix with Microsoft Excel

      1. pedrowave

        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.

  1. Pingback: Better Option Button Controls | Option Explicit VBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s