Specifiyng changes in the checkboxes

Question

I am struggling with linking ticks in checkboxes to values in other list and making formatting based on this afterwards.

(i. e. person clicks the checkbox and the cell turns grey)

So, I have a number of check boxes, each text box is labeled as

"Check Box 1" ... "Check Box 44"

    Sub Link()
    Dim i As Long
    Dim checkbox(1 To 44) As checkbox

    For i = 1 To 44
        For Each element In checkbox
        ActiveSheet.Shapes.Range(Array(element)).Select
            With Selection
            .Value = xlOn
            .LinkedCell = "Calculation!$A$" & "i"
            .Display3DShading = False
            End With
        Next
    Next

    End Sub

But it seems I am either selecting checkboxes wrong, or either assign their array in a wrong way. Any help would be very much appreciated.

You link to`Calculation!$A$i`, instead of `Calculation!$A$1`...`Calculation!$A$44`with`.LinkedCell = "Calculation!$A$" & i`. And avoid [Select/Selection](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — ComputerVersteher, Nov 01 '19 at 13:40
I would use a checkboxes collection instead of an array. Also I think `checkbox()` should be declared as a variant. And btw it's a bad practice to use reserved names for your variables. — Stavros Jon, Nov 01 '19 at 13:45

score 1 · Accepted Answer · answered Nov 01 '19 at 13:51

ComputerVersteher has nailed your basic problem, but you can adopt a slightly simpler approach thus:

Sub x()

Dim i As Long

For i = 1 To ActiveSheet.CheckBoxes.Count
    With ActiveSheet.CheckBoxes(i)
        .Value = xlOn
        .LinkedCell = "Calculation!$A$" & i
        .Display3DShading = False
    End With
Next i

End Sub

This does assume the checkboxes are named in order (which by default they should be).

All that said, I'm not sure what exactly you're trying to do.

Specifiyng changes in the checkboxes

1 Answers1