select all checkbox in a sheet

[nasim12w]

hi,
i have a sheet that have 100 checkbox. i want to select(not check) all checkbox for change their property.

how can i select all? (by macro or other way)

[rishikrsaw]

Sub SelectAllCheckBox()
Dim CB As CheckBox

For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
CB.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
End If
Next CB

End Sub

[rishikrsaw]

and then use

Sub Mixed_State()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 1").Value And ActiveSheet.CheckBoxes("Check Box 1").Value <> 2 Then
ActiveSheet.CheckBoxes("Check Box 1").Value = 2
Exit For
Else
ActiveSheet.CheckBoxes("Check Box 1").Value = CB.Value
End If
Next CB
End Sub

[nasim12w]

Sub SelectAllCheckBox()
Dim CB As CheckBox

For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
CB.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
End If
Next CB

End Sub

thanks, can you explain this macro row by row?
i want to use this macro in my file, but i am not sure if this change other thing in my file or not.

in other word, i want to use this macro to uncheck all checkbox in a sheet.

[nasim12w]

and then use

Sub Mixed_State()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 1").Value And ActiveSheet.CheckBoxes("Check Box 1").Value <> 2 Then
ActiveSheet.CheckBoxes("Check Box 1").Value = 2
Exit For
Else
ActiveSheet.CheckBoxes("Check Box 1").Value = CB.Value
End If
Next CB
End Sub

did not work

[rishikrsaw]

First of all add, a checkbox to your sheet. You can add the checkbox by navigating to Developer Tab > Insert > Checkbox. Title this checkbox as “Select All”, this will be your Master Checkbox.

Now, press Alt + F11, to open the VBA Editor, navigate to Insert > Module and paste the below code.

Sub SelectAll_Click()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
CB.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
End If
Next CB
End Sub

Note: Make sure that the name of your Master Checkbox is “Check Box 1”. If not, then change the “Check Box 1” in the above code with the name of your Master Checkbox.

[nasim12w]

First of all add, a checkbox to your sheet. You can add the checkbox by navigating to Developer Tab > Insert > Checkbox. Title this checkbox as “Select All”, this will be your Master Checkbox.

Now, press Alt + F11, to open the VBA Editor, navigate to Insert > Module and paste the below code.

Sub SelectAll_Click()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
CB.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
End If
Next CB
End Sub

Note: Make sure that the name of your Master Checkbox is “Check Box 1”. If not, then change the “Check Box 1” in the above code with the name of your Master Checkbox.

it just uncheck all,or check all. not select all!!
i want to select all,not checkmark all.

[rishikrsaw]

hey or selecting all the check box just simply press f5 then goto object and select option and ok

as result u have selected all check box..............

[nasim12w]

thanks, i solve my problem by other way. but thanks a lot