Vba

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim cbValues As String
    Dim optionLabelsRange As Range
    Dim checkboxLinksRange As Range
    Dim monitoredColumn As Long
    Dim currentRow As Long
    Dim i As Long
    
    Set ws = Me
    monitoredColumn = 7 ' Column G
    
    ' Define your option labels and linked checkbox cells
    Set optionLabelsRange = ws.Range("B2:B10") ' Options for all clients
    Set checkboxLinksRange = ws.Range("Z2:Z10") ' Linked cells for checkboxes

    ' Trigger only when a single cell is changed in Column G
    If Not Intersect(Target, ws.Columns(monitoredColumn)) Is Nothing Then
        If Target.Cells.Count = 1 And Target.Value <> "" Then
            currentRow = Target.Row

            ' Store checkbox selections for the *previous* client
            If currentRow > 2 Then
                cbValues = ""
                For i = 1 To checkboxLinksRange.Cells.Count
                    If checkboxLinksRange.Cells(i).Value = True Then
                        cbValues = cbValues & optionLabelsRange.Cells(i).Value & ", "
                    End If
                Next i

                ' Trim trailing comma and space
                If Len(cbValues) > 2 Then cbValues = Left(cbValues, Len(cbValues) - 2)

                ' Save in Column H of previous client row
                ws.Cells(currentRow - 1, "H").Value = cbValues
            End If

            ' Reset all checkboxes
            checkboxLinksRange.Value = False
        End If
    End If
End Sub