Excel - Create Marlett checkboxes Group Functionality

Question

I am creating checkbox functionality using Excel cells. I've been working on VBA code that:

1. Allows double-click functionality on a cell which enters (or removes) a Marlett check/ tick represented with an "a" in the cell value. I.e., by double-clicking the cell "a" is either entered or removed.
2. The double-click functionality works on a group of cells B1:B10, where:
3. Double-clicking cell B1 will toggle the "a" in both cell B1 and cells B2:B10 collectively, and
4. Double-clicking on any cell B2:B10 will individually toggle between entering and removing "a" in that particular cell.

You could think of B1 being the 'master' controller (eventually I would like this cell to represent a category heading) and B2:B10 to be items within the category that could be individually selected or deselected. Here is the working code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Me.Range("B2:B10")) Is Nothing Then
        Cancel = True
        If Target.Value = "a" Then
            Target.Value = vbNullString
        Else
            Target.Value = "a"
            Target.Font.Name = "Marlett"
        End If
    ElseIf Not Intersect(Target, Me.Range("B1")) Is Nothing Then
        Cancel = True
        If Me.Range("B1").Value = "a" Then
            Me.Range("B1").Value = vbNullString
            Me.Range("B2:B10").Value = vbNullString
        Else
            Me.Range("B1").Value = "a"
            Me.Range("B2:B10").Value = "a"
            Me.Range("B2:B10").Font.Name = "Marlett"
        End If
    End If
End Sub

This works fine for 1 group of rows (B1:B10), but I want to extend this code to multiple groups of rows, where there is 1 'master' controlling row within the group, but the individual rows within the group can also be individually controlled. So for example, in an new additional group, B11:B20, the B11 cell would toggle the "a" in both cell B11 and cells B12:B20 collectively, and B12:B20 could individually toggle between entering and removing "a" in each particular cell. And this functionality could repeat for more additional groups if they are added (e.g., B21:28, B29:35, etc and so on). Just for clarity - Each group would not impact on the functionality of any other group.

I'm fairly new to coding so I'm stumped and would appreciate any solutions offered.

Answer 1

Make a Subroutine that handles one group.

Sub HandleCheckboxRange(target As Range, masterCell As Range, groupRange As Range)
    Const checked = "a"

    If Intersect(target, groupRange) Is Nothing Then Exit Sub
    Dim newValue As String
    newValue = IIf(target = checked, vbNullString, checked)
    
    If Not Intersect(target, masterCell) Is Nothing Then
        ' Master cell was clicked: Set the whole range
        groupRange.Value = newValue
        groupRange.Font.Name = "Marlett"
    Else
        ' Any other cell of range was clicked: Set only this cell
        target.Value = newValue
        target.Font.Name = "Marlett"
    End If
End Sub

The routine expects 3 parameter: The first is the cell that was clicked (target), the second defines the master cell for a group, the third the range for the complete group (including the master cell).

In the DoubleClick-Event, call this subroutine for every group you want to handle:

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
    HandleCheckboxRange target, Range("B1"), Range("B1:B10")
    HandleCheckboxRange target, Range("B11"), Range("B11:B20")
    HandleCheckboxRange target, Range("B21"), Range("B21:B28")
    ' (and so on)
    Cancel = True
End Sub