Check all

Hello all,

I have a spreadsheet that if I dont find anything wrong with a fire extinguisher I click yes. But there is 150 extinguishers with 4 colums, and that is a lot of clicking. Is there a way I can add a button for each colum that I can just click that button to put yes in all rows?

Thank you for any help.

Here is the form.

Checkboxes are objects and although you have them arranged to display over a cell in a column, in reality they are not part of a cell. I mention this because you can't use VBA to loop down the rows in a column and set the checkbox just for a specific column - at least as far as I know.

You would have to either process them all in one go or individually.
This code will turn them all on, then you can deselect the exceptions.

Sub Chkbox_On()
     
    Dim sChkbx As Shape
     
    For Each sChkbx In ActiveSheet.Shapes
        If sChkbx.Type = msoFormControl Then
            If sChkbx.FormControlType = xlCheckBox Then
                If sChkbx.ControlFormat.Value = xlOff Then
                    sChkbx.ControlFormat.Value = xlOn
                End If
            End If
        End If
    Next sChkbx
     
End Sub

This seems to work. Add new checkboxes at the top of desired columns and make sure they are left-aligned with those below and assign this macro to them.

Sub x()

Dim ctl As Excel.CheckBox, ChkBox As CheckBox

Application.ScreenUpdating = False

Set ChkBox = ActiveSheet.CheckBoxes(Application.Caller)
    
For Each ctl In ActiveSheet.CheckBoxes
    If ctl.Left = ChkBox.Left Then
        ctl.Value = ChkBox.Value
    End If
Next ctl

Application.ScreenUpdating = True

End Sub