How to "tag" cells so they are automatically recognized as part of a group?

[Kelvin Stott]

Hi,

I have written (adapted) the following code to turn specific cells into checkboxes, which works very nicely:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim CheckCells As Range, CheckCell As Range
    Set CheckCells = Intersect(Target, Range("CheckBoxes"))

    If Target.Cells.Count > 1 Then Exit Sub 'Remove this line to enable multi-select
    If CheckCells Is Nothing Then Exit Sub

    For Each CheckCell In CheckCells
            If CheckCell = vbNullString Then
                CheckCell = "a"
            Else
                CheckCell = vbNullString
            End If
    Next CheckCell

End Sub

Now, I would like to automatically assign cells to a named "CheckBoxes" range just by copying an existing checkbox cell, without having to edit a named range of cells (or worse, define specific range addresses given that they may be moved around).

Is there any way to assign some kind of "tag" to a cell, so that it will automatically be assigned to a named range when moved or copied to another part of the sheet or workbook? Clearly, it would then need to work with the code above.

I would really appreciate any help, thanks!

 

[Kelvin Stott]

Just one more thought: the cells I want to tag would all have the same unique font (Marlett), in case this fact can be used to identify them and assign them to the same named group?

At least the font would be copied over, so I was thinking this might be one way to tag them...

Any thoughts?

 

[Kelvin Stott]

Never mind, I tried this and it works like a charm:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim CheckCell As Range

If Target.Cells.Count > 1 Then Exit Sub

For Each CheckCell In Target
If CheckCell.Font.Name = "Marlett" Then
If CheckCell = vbNullString Then
CheckCell = "a"
Else
CheckCell = vbNullString
End If
End If
Next CheckCell

End Sub

 

[Kelvin Stott]

Simplified version:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count = 1 And Target.Font.Name = "Marlett" Then
If Target.Value = vbNullString Then
Target.Value = "a"
Else
Target.Value = vbNullString
End If
End If

End Sub