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

 

[L. Howard]

That is true, the same macro to all boxes in a column, with the total cell changed for each column.
Your picture showed four columns with about 20 boxes. You say you have 30 columns now. So that would be 30 'peat for the macro, each with a separate total cell.
Your actual sheet must be much larger and would be a huge task.

Counting the linked cells seems like a much less burdensome task to set up.

Howard

 

[FormR]

Hi, if code was preferable, I think these small changes to Howard's code would update the appropriate columns total.

Sub Macro_On_Off_Click_1()
    
    With ActiveSheet.Shapes(Application.Caller)
       
        If .ControlFormat = xlOn Then
           Cells(28, .TopLeftCell.Column).Value = Cells(28, .TopLeftCell.Column).Value + 1
          Else
           Cells(28, .TopLeftCell.Column).Value = Cells(28, .TopLeftCell.Column).Value - 1
        End If
        
    End With
    
End Sub

 

[My Aswer Is This]

If you were to tell us what your trying to do we could provide a better way then needing 270 checkboxes on your sheet.
What is your determining factor as to checking the box or not checking the box

 

[My Aswer Is This]

Actually my math was wrong. You have 2,700 checkboxes
I'm sure there is a better way to do things

 

[marcialfa]

Some more info regarding this sheet.
Is for the school I work to assess pupils regarding their phonics abilities. There are 30 pupils in a class, that’s why 30 columns and there are around 90 rows with 90 checkboxes.
If a child is able recognise a sound we tick the box; down under the last row of checkboxes I want a total/sum of how many sounds the child was able to recognise and then I want to do an average or so and maybe a list with what sounds they find difficult.
I know that is it a bit hard but if I can offer more info please do ask for.
Thank you guys for your help !

 

[My Aswer Is This]

Are you talking about columns 1 to 30
Are you willing to consider another approach which would require no check boxes
You just double click the cell if the student recognise a sound
And a total number in entered in the last row under the students name.
And when we double click the cell the interior color of the cell turns green

This would give us totals.
We could work out averages also.
And create a report also,
This double clicking only requires a little bit of code and requires no text boxes.
If you would consider this I can write you a script.
I assume we would start this on row(2) of each column 1 to 30
Assume students name in in Row(1) of each column

Would this work?

 

[My Aswer Is This]

Here is how I think you should do this which requires no checkboxes.

Put you student names in row(1) starting in column (1) and continue on to column (30)
Leave rows two and three empty

Put your sounds in your columns starting in Row(4)

Now with this script all you need to do is double click on the sound if the student get's it correct.
You will see a number shows up in that column row(1) showing the student got it correct. This number will increase by one every tine you double click a cell. Your not allowed to double click the same cell twice.
And in row(3) you will see the percentage he got correct so far.
And the cell you double clicked on will turn Green.
It's better to have the counts in the upper rows then the last row.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column < 31 And Target.Row > 3 Then
Cancel = True
Dim lastrow As Long
Dim More As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
More = lastrow - 3
If Target.Interior.ColorIndex = 4 Then MsgBox "You have allready answered that question": Exit Sub
Cells(2, Target.Column).Value = Cells(2, Target.Column).Value + 1
Cells(3, Target.Column).Value = Cells(2, Target.Column).Value / More
Target.Interior.ColorIndex = 4
End If
End Sub

 

[marcialfa]

Superb ! I will try this on Monday when I am back to school !
Thank you very much !

 

[My Aswer Is This]

Not sure who your talking to several here have posted answers here. Let us know how this works for you.

Superb ! I will try this on Monday when I am back to school !
Thank you very much !

 

[marcialfa]

Hello there,

I have just tried your code and works well.. I have few questions though, if that`s ok !

Can I add a Clear Button ? and if one cell is marked by mistake how can I undo it ?
Once I saved the file with the macro enabled when I give the file to a teacher to fill in the form I want the macro to be running straight away.

Regarding the code... at the If condition, can the target start from column 3 to 33 ? something like this If (Target.Column < 33 and Target.Column > 2 ) and Target.Row > 3 Then

Thank you so much !