Selecting all check boxs in a sheet by clicking only one

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
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!
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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