Excel table with columns checkboxes

marcialfa

New Member
Joined
Jan 11, 2017
Messages
12
Hello,

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

d2Hh7m

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
d2Hh7m
https://ibb.co/d2Hh7m
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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)
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
Hi, no - better as forms control IHMO.

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

Code:
Sub m()
Dim cb As CheckBox
For Each cb In ActiveSheet.CheckBoxes
    cb.LinkedCell = cb.TopLeftCell.Address
Next cb
End Sub
 
Upvote 0
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

Code:
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
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top