Checkbox Series to create DataBar Sets

[NinaE_11]

Hello,
I'm attempting to create a list with four columns of checkboxes, adding up the "TRUE" in each row, left to right, to formulate a databar visual at the end of each row.

I've got a very long list, so manually linking each contiguous manually, for four columns, could be quite time consuming. I've found some VBA language that will link contiguous cells for each row, but it will only work when there is only one column of checkboxes. i.e. - the below example would work if checkboxes were in Column A, and I wanted a "TRUE" in Column B. What I can't figure out is how to move these same details for checkboxes in Column C and a second TRUE/FALSE outcome in Column D.

Sub LinkChecks()
'Update by Extendoffice
Dim xCB
Dim xCChar
i = 2
xCChar = "B"
For Each xCB In ActiveSheet.CheckBoxes
If xCB.Value = 1 Then
Cells(i, xCChar).Value = True
Else
Cells(i, xCChar).Value = False
End If
xCB.LinkedCell = Cells(i, xCChar).Address
i = i + 1
Next xCB
End Sub

Any insights would be much appreciated! Thank you for taking the time to help!

 

[sijpie]

If you have your worksheet with all the checkboxes set up, then add the code below to a module and run it once.

If you still need to set up your checkboxes:

1. Add a Formcontrol checkbox to the first cell. Ensure the the checkbox is completely in one cell.
2. Select this cell and the cells below it which need to get checkboxes.
3. Press Ctrl-D
4. Now select this range and the three columns to the right
5. Press Ctrl-D

Now you have your four columns of checkboxes.

Run the code below once.

Once you have run the code, click a checkbox. You will see 'TRUE' behind the checkbox. If you click it again, it says 'FALSE'.
In the add up column, put the formula to sum up the values of the four cells to the left (for instance =SUM(B3:E3))
Copy the formula down.

Now this column gives the sum of the checked boxes in each row.

Next, for appearances, select all the cells with the checkboxes and set the font colour to the background colour, so the user does not see the TRUE or FALSE.

That's it.

If you add more checkbox rows later, just rerun the macro.

Option Explicit

Sub LinkCheckBx()
'Links each checkbox to the cell in which it sits
    Dim cbxCB As Object
   
    For Each cbxCB In ActiveSheet.CheckBoxes
        cbxCB.LinkedCell = cbxCB.TopLeftCell.Address
    Next cbxCB
End Sub

 

[NinaE_11]

Outstanding! Cant' thank you enough!