One Checkbox to uncheck/disable all others in group

[tomnice]

Hi there,
I'm trying to create a macro that will uncheck checkboxes in a group when one is selected - this checkbox is labelled as (All). When selected, I want the other checkboxes to be disabled so that it is only the (All) box that can be checked. Once that is unchecked, the other cbs can be checked once again.

I've got to the stage where clicking the (All) box unchecks the other cbs but have had trouble in disabling other cbs and getting them to enable again.

Here is my code (I'm very new to VBA so is quite messy and I have named the cbs from CB_07 to CB_14 with CB_07 being the (All) checkbox):

Sub ProcessGrps()
Dim shpChkBox As Shape
Set shpChkBox = ActiveSheet.Shapes(Application.Caller)
If shpChkBox.ControlFormat.Value = xlOn Then 'xlOn is Checked
Select Case shpChkBox.Name
Case "CB_07"
With ActiveSheet
.Shapes("CB_08").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_09").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_10").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_11").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_12").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_13").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_14").ControlFormat.Value = xlOff 'xlOff is Unchecked
End With

End Select
ActiveSheet.Shapes(shpChkBox.Name).ControlFormat.Value = xlOn 'xlOn is Checked

End If
End Sub
Thanks in advance for any help, it is greatly appreciated!

Tom

[ranman256]

Dim chk As Object
For Each chk In ActiveSheet.CheckBoxes
chk.Value = false
Next
Set chk = Nothing

[tomnice]

Dim chk As Object
For Each chk In ActiveSheet.CheckBoxes
chk.Value = false
Next
Set chk = Nothing


Thanks for your response but this isn't sufficient as i need the checkbox to deselect all others with those others being disabled until this checkbox is unchecked. I have made further developments with the code - now I can make sure the other cbs are disabled while CB_07 is checked but only for a few clicks and then they become enabled again. Do you guys know how to keep them disabled while CB_07 is checked?


Sub ProcessGrps()
'
Dim shpChkBox As Object


Set shpChkBox = ActiveSheet.Shapes(Application.Caller)

If ActiveSheet.Shapes("CB_07").ControlFormat.Value = xlOn Then
With ActiveSheet
.Shapes("CB_08").ControlFormat.Enabled = False 'xlOff is Unchecked
.Shapes("CB_09").ControlFormat.Enabled = False 'xlOff is Unchecked
.Shapes("CB_10").ControlFormat.Enabled = False 'xlOff is Unchecked
.Shapes("CB_11").ControlFormat.Enabled = False 'xlOff is Unchecked
.Shapes("CB_12").ControlFormat.Enabled = False 'xlOff is Unchecked
.Shapes("CB_13").ControlFormat.Enabled = False 'xlOff is Unchecked
.Shapes("CB_14").ControlFormat.Enabled = False
End With
Else
With ActiveSheet
.Shapes("CB_08").ControlFormat.Enabled = True 'xlOff is Unchecked
.Shapes("CB_09").ControlFormat.Enabled = True 'xlOff is Unchecked
.Shapes("CB_10").ControlFormat.Enabled = True 'xlOff is Unchecked
.Shapes("CB_11").ControlFormat.Enabled = True 'xlOff is Unchecked
.Shapes("CB_12").ControlFormat.Enabled = True 'xlOff is Unchecked
.Shapes("CB_13").ControlFormat.Enabled = True 'xlOff is Unchecked
.Shapes("CB_14").ControlFormat.Enabled = True
End With

End If

If ActiveSheet.Shapes("CB_07").ControlFormat.Value = xlOn Then 'xlOn is Checked


Select Case shpChkBox.Name


Case "CB_07"
With ActiveSheet
.Shapes("CB_08").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_09").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_10").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_11").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_12").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_13").ControlFormat.Value = xlOff 'xlOff is Unchecked
.Shapes("CB_14").ControlFormat.Value = xlOff 'xlOff is Unchecked


End With


End Select
ActiveSheet.Shapes(shpChkBox.Name).ControlFormat.Value = xlOn 'xlOn is Checked



End If
End Sub

Many thanks

[SamT]

His code refers to the CheckBox as a Control, where your code refers to it as a drawing, (Shape.) Maybe there is a difference in the way Checkboxes act, depending on how they are refered to. :dunno