Checkbox that unchecks and disables others in its group
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
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago