Accessing HTML controls in Excel

[thectosian]

Hi,
I cut and pasted an online survey from my website into an excel spreasheet.
The page has a number of checkbox options which when one is selected, the others in the group are deselected, as expected.

I can 'see' the checkboxes as "Control 1" to "Control 19" using the code below.
What I can't figure out is how to access the checkbox to determine which control has its checked attribute set to true.

==== Code===
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 7) = "Control" Then
Sheets("Results").Cells(1, 1) = ":" & shp.Name & ":"
End If
Next shp

The frustrating bit is that I can see the Checked attribute in the VBA Properties list, [in development mode], so I figure that if it can be seen there, I should be able to get at it.
I'm aware that, unlike VB.Net, the CType operator doesn't work in VBA.

Can you help?

regards,
Duncan.

 

[diddi]

hi duncan, welcome to the board. i hope you find it interesting and challenging.
this might get you started...

    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If Left(shp.Name, 7) = "Control" Then
            Sheets("Results").Cells(1, 1) = ":" & shp.Name & ":"
            if shp.value=true then msgbox shp.name & " is selected"
        End If
    Next shp

 

[thectosian]

Thanks for the quick response, appreciated.

Unfortunately, the "shp.Value" term returns a run-time error 438; "Object doesn't support this property or method". the Value property isn't in the list of properties seen at http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.shape_members.aspx for the Shape object.

This is why I mentioned the CType function earlier as I had hoped that referencing the checklbox type would allow me to access the value property in that.

regards,
Duncan.