Selecting all check boxs in a sheet by clicking only one

[masud8956]

Hi,

I have 60 check boxs in 60 consecutive rows (same column) in 12 sheets of my workbook.

The 60 check boxs are linked to individual cells (2 columns to the right). Is there any way that I add a 61st (master!) check box which will be linked to rest 60, so I can check/uncheck all 60 just by clicking the 61st? And when I keep it unchecked, all 60 check boxs show individual selections.

I saw some solutions of similar kind using VBA. But I do not prefer VBA solution. Is there any other way to do it?

Regards!

 

[mikerickson]

Quick answer, No.
If you want the Master Checkbox to act that way, VBA is required.

One Question, if the Master is checked, you want all the others to become checked. Good.

At that point with Master and all the servant boxes checked, if the user clicks on one of the servant boxes, do you want:
A) both the clicked servant and the Master to become unchecked
or
B) do you want a checked Master to disable the servant checkboxes so that they can't be un-checked?

Two Question, if the user checks all of the servant boxes individually, do you want the master to be automatically checked?

 

[masud8956]

Quick answer, No.
If you want the Master Checkbox to act that way, VBA is required.

Thanks for the quick answer!

Ans to Question One: (A). I want both the clicked servant and the Master to become unchecked.

Ans to Question Two: If the user checks all of the servant boxes individually, I do not want the master to check itself automatically.

 

[mikerickson]

This assumes that you have Forms Menu checkboxes, and the master box is named Check Box 1 (change to match your actual situation)
Assgn the first macro to the master checkbox, and the other to all other checkboxes.

Sub MasterBox_Click()
    Dim oneShape As Shape
    If ActiveSheet.Shapes(Application.Caller).ControlFormat.Value = xlOn Then
        For Each oneShape In ActiveSheet.Shapes
            With oneShape
                If .Type = msoFormControl Then
                    If .FormControlType = xlCheckBox Then
                        oneShape.ControlFormat.Value = xlOn
                    End If
                End If
            End With
        Next oneShape
    End If
End Sub
Sub SubordinateBox_Click()
    With ActiveSheet
       If .Shapes(Application.Caller).ControlFormat.Value = xlOff Then
           .Shapes("Check Box 1").ControlFormat.Value = xlOff
       End If
    End With
End Su

b

 

[masud8956]

It worked nicely!

Thank you very much!!