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 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.
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 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.
Get the download file
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
A year ago I made a chess game without macros in Excel using the technique described in their article, with a 8×8 dynamically linked square matrix.
You can see the result on my blog: Data Conversion 3: Chess Game with formulas
http://pedrowave.blogspot.com/2012/08/data-converter-3-chess-game-with.html
Iterative calculation – that’s a great idea!
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.