Solved: Easiest way to check/uncheck all checkboxes?

[mae0429]

Hi, I have this crossposted on Mr. Excel:
http://www.mrexcel.com/forum/showthread.php?t=331524

I've got an embedded chart on a worksheet and it has 24 checkboxes (control toolbox, not form) corresponding to different devices. How would I go about linking another checkbox to select or unselect all the boxes?

I know it could generally be something like:
[VBA]
Private Sub checkbox_Click()
Select Case checkbox.Value
Case True
Make all the other checkboxes true
Case False
Make all the other checkboxes false
End Select
End Sub
[/VBA]

And how the heck do I refer to the checkboxes?!?

Thanks!
-Matt

[Bob Phillips]

[vba]

Private Sub CheckBox1_Click()
Dim obj As OLEObject

For Each obj In Me.OLEObjects

If TypeName(obj.Object) = "CheckBox" Then

If obj.Name <> "CheckBox1" Then

obj.Object.Value = Me.CheckBox1.Value
End If
End If
Next obj
End Sub
[/vba]

[mae0429]

Problem here is that, apparently, when using control toolbox checkboxes on an embedded chart, they are part of the Shapes collection.

[mae0429]

Solved.

[VBA]
Sub MatchCheckbox()
Dim s As Shape
Dim i As Long
Worksheets("Chart").ChartObjects(1).Activate
With ActiveChart
For i = 8 To 31
.Shapes(i).ControlFormat.Value = .Shapes(32).ControlFormat.Value
Next i
End With
End Sub
[/VBA]

[Bob Phillips]

Problem here is that, apparently, when using control toolbox checkboxes on an embedded chart, they are part of the Shapes collection.

And where did you mention an embedded chart?

[mae0429]

From my first post:

I've got an embedded chart on a worksheet and it has 24 checkboxes (control toolbox, not form) corresponding to different devices.