How to loop through ActiveX Controls placed on sheet

[szita2000]

Hi guys.
I am building a loop that will filter certain things within a date range.
I have to put some basic controls on the worksheet where the data is.
In this fashion.

What I'm trying to do is to disable the optionbuttons when the user enters a date in to the textboxes (I did not use datepicker, as I had trouble registering them on other PCs)

I would like to loop through the controls, however I can't get to them
What is the correct syntax to do this?
When I debug It gives me a type mismatch error highlighting the "OptionButton" bit.

Option Explicit

Sub test()

    Dim ctrl As OLEObject

    
        For Each ctrl In Worksheets("CL Check List File").OLEObject
            Debug.Print ctrl.Name
            If TypeName(ctrl) Is "OptionButton" Then

                ctrl.Enabled = False
            End If

        Next ctrl

End Sub

Thanks

Thomas

 

[Anthony47]

TypeName returns a string, so try using

If TypeName(ctrl) = "OptionButton" Then

 

[szita2000]

TypeName returns a string, so try using

If TypeName(ctrl) = "OptionButton" Then

Thanks!

Now it stops at the first line of the For loop.
Error 438 - Object Doesn't support this property or method.
Maybe I can't operate with OLEObjects on a worksheet?
Solved it! It was the wrong syntax!!!

Instead of:

For Each ctrl In Worksheets("CL Check List File").OLEObject

I needed

For Each ctrl In Worksheets("CL Check List File").OLEObjects 'As it is a collection of controls.

Thanks a lot Anthony47!

 

[szita2000]

Just for good measure.

The correct Syntax that works is:

Sub test()

    Dim ctrl As OLEObject

    
        For Each ctrl In Worksheets("CL Check List File").OLEObjects
            Debug.Print ctrl.progID
            If ctrl.progID = "Forms.OptionButton.1" Then

                ctrl.Enabled = False
            End If
        
        
        
        Next ctrl

End Sub

 

[Anthony47]

Google, try, google, retry; that's the path to get it running...
...and thank you for the feedback