Using Excel 2010
I have a worksheet which contains three ActiveX frames. Each of these frames contains two or more OptionButtons. A reset button on the worksheet resets the values of the optionbuttons to 'False'.
Now, I can reset them all using a separate For loop for each frame:
Private Sub CommandButtonReset_Click()
'This button resets all the OptionButtons to False (unchecked)
Dim x As Control
For Each x In Frame1.Controls
x.Value = False
Next
For Each x In Frame2.Controls
x.Value = False
Next
For Each x In Frame3.Controls
x.Value = False
Next
End Sub
...but I'd like to reset them all using one nested For loop, like this:
Dim xControl as control
Dim xFrame as Frame
For Each xFrame in (ActiveSheet.Frames? .Shapes? .OLEObjects?)
For Each xControl in xFrame
xControl.Value = False
Next
Next
After extensive searching online and through books, I can't find the correct way to access each ActiveX frame in the active worksheet.
Try this:
Sub Tester()
Dim o As OLEObject, c
For Each o In Sheet1.OLEObjects
'is this a Frame?
If TypeName(o.Object) = "Frame" Then
For Each c In o.Object.Controls
'is this a checkbox?
If TypeName(c) = "CheckBox" Then
c.Value = False
End If
Next
End If
Next o
End Sub