Excel table with columns checkboxes

[marcialfa]

Hello,

If anyone can help me with this matter I will be very grateful.

What I have is a list of columns with checkboxes and I want at the bottom of each column to be a total !


Thank you!

here is a picture of the file

https://ibb.co/d2Hh7m

 

[FormR]

Hi, you could create cell links for those checkboxes that link them to the cell they are placed in - format the cells font to white (to hide the TRUE/FALSE values) and then use, for example:

=COUNTIFS(D2:D26,TRUE)

 

[marcialfa]

Thank you ! That is quite a good Idea with the font colour ! I do have though 90 checkboxes per column and 30 columns ! Do I have hat to link each one of the cells manually ?

 

[FormR]

Are they "Form Controls" or "Active X" check boxes?

 

[marcialfa]

I think that they are Form Control but I can change them to Active X if I there is an easier way to fulfil this ! Thanks

 

[FormR]

Hi, no - better as forms control IHMO.

You can try running this whilst the sheet with the checkboxes is the active sheet.

Sub m()
Dim cb As CheckBox
For Each cb In ActiveSheet.CheckBoxes
    cb.LinkedCell = cb.TopLeftCell.Address
Next cb
End Sub

 

[FormR]

but I can change them to Active X

Wouldn't that be slower than manually setting the linked cells anyway

 

[marcialfa]

Amazing, thank you very much did work so far !!

 

[L. Howard]

One way, would be to assign this macro to all the checkboxes in column C.
When any box is checked, it adds 1 to D28, when unchecked it subtracts 1.

Do the same for the other columns, changing the total cells for each column of boxes.

Perhaps you do not need the subtract for unchecking a box. If so you can comment it out like this 'Range("D28") = Range("D28") - 1 or delete the line.

Howard

Sub Macro_On_Off_Click_1()
    
    With ActiveSheet.Shapes(Application.Caller)
       
        If .ControlFormat = xlOn Then
           Range("D28") = Range("D28") + 1
          Else
           Range("D28") = Range("D28") - 1
        End If
        
    End With
    
End Sub

 

[marcialfa]

Hi there,

Thank you for your support.

But this Macro is only for the column D, when I have 90 Rows and 30 Columns .. so this means that I have to implement this macro for 30 times ? for each one of the columns ?