Getting value of an activeX option button

[nemmi69]

Hi,

I am using the following code

Sub WhichOption()    For Each objX In ActiveSheet.OLEObjects
        If objX.Enabled = True And objX.Visible = True And TypeName(objX.Object) = "OptionButton" Then
            If objX.Control.Value = True Then
                MsgBox "Name = " & objX.Name & "  Typ = " & TypeName(objX.Object) & " is selected"
                Exit For
            End If
        End If
    Next objX


End Sub

The line "objX.Control.Value = True" returns the error "Run-time error '438': Object doesn't support this property or method."

Any ideas?

 

[dmt32]

Hi,
try this update to your code

Sub WhichOption()
    Dim objX As OLEObject
    For Each objX In ActiveSheet.OLEObjects
        If TypeName(objX.Object) = "OptionButton" Then
            If objX.Enabled = True And objX.Visible = True Then
                If objX.Object.Value Then
                    MsgBox "Name = " & objX.Name & "  Typ = " & TypeName(objX.Object) & " is selected"
                    Exit For
                End If
            End If
        End If
    Next objX
End Sub

Dave

 

[nemmi69]

Hi,
try this update to your code

Sub WhichOption()
    Dim objX As OLEObject
    For Each objX In ActiveSheet.OLEObjects
        If TypeName(objX.Object) = "OptionButton" Then
            If objX.Enabled = True And objX.Visible = True Then
                If objX.Object.Value Then
                    MsgBox "Name = " & objX.Name & "  Typ = " & TypeName(objX.Object) & " is selected"
                    Exit For
                End If
            End If
        End If
    Next objX
End Sub

Dave

Cheers Dave, works a treat! Any guesses what tripped it up? I was checking I was looking at an option button.

 

[Rick Rothstein]

Sub WhichOption()    For Each objX In ActiveSheet.OLEObjects
        If objX.Enabled = True And objX.Visible = True And TypeName(objX.Object) = "OptionButton" Then
            If objX.[B][COLOR="#FF0000"]Control[/COLOR][/B].Value = True Then
                MsgBox "Name = " & objX.Name & "  Typ = " & TypeName(objX.Object) & " is selected"
                Exit For
            End If
        End If
    Next objX
End Sub

The line "objX.Control.Value = True" returns the error "Run-time error '438': Object doesn't support this property or method."

Any ideas?

Change the red highlighted text to Object instead of Control and it should then work.

 

[dmt32]

Cheers Dave, works a treat! Any guesses what tripped it up? I was checking I was looking at an option button.

This line

If objX.Control.Value = True Then

You were trying to use a property which is not available to the object.

and whilst this line may have worked

If objX.Enabled = True And objX.Visible = True And TypeName(objX.Object) = "OptionButton" Then

personally, I prefer to test for the object I am working with first & then proceed with other tests.

 If TypeName(objX.Object) = "OptionButton" Then
         'do other tests etc
  End If

hence my alteration - but if it was working for you, then your choice

Also, always better if you declare your variables with the appropriate data type.

Dave