vbaexcelactivex

VBA Excel - How to Access Control within Frame within Worksheet? Refactor / Optimize


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.


Solution

  • 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