HTMLChec objects in my Worksheet hierarchy, but Excel VBA can't find them

[dmars]

Title almost says it all. I've been bugged by the form controls that show up when you copy a webpage and paste it into Excel - checkboxes, radio buttons, textboxes, probably all the standard form controls do this, I just haven't seen them all.

ctrl-G:Objects does not select them, and no ordinary means of selecting and deleting objects will delete them. Deleting rows or columns they appear to be in doesn't delete them. The only solution by hand that I've found is to copy the data I want onto a new sheet and then delete the sheet that has the form controls on it.

I'm guessing this is what Excel does with form controls from the web that it recognizes as form controls, but can't map them to its builtin equivalents.

Looking at the locals for a macro I'm writing for a sheet that has these checkboxes, I see an HTMLChec object in the worksheet's hierarchy for each of these checkboxes on the sheet. Excel gives their type as HTMLCheckbox/HTMLCheckbox and they're all named HTMLChec - no enumeration and no way to obviously distinguish them from each other without going into the properties of each one

I grabbed the HTMLName property off one of them, selected at random, and it is: "ctl00$guB$ucLeadResults$rptLeads$ctl00$chkDel". I can get other properties if that would be useful in any way, just ask.

Here's my test code:

Sub ProcessNew()

Dim wksht As Worksheet, htmlchk As Object

Set wksht = ActiveWorkbook.Worksheets("new")

'delete all checkboxes in worksheet
For Each c In wksht.CheckBoxes
    c.Delete
Next

'assign an HTMLChec object to a variable
Set htmlchk = wksht[COLOR=#008080].HTMLChec[/COLOR]

End Sub

The first thing I tried was a standard loop through the worksheet's Checkboxes, intending to delete them one by one. But Excel simply skipped over the loop, and looking at the worksheet's hierarchy, there are no objects of type Checkbox in it, so Excel just didn't find anything to do in that loop.

The second thing I tried was assigning the worksheet.HTMLChec object to an object variable, hoping that maybe it would grab the first one (or any of them) and I could see what might be done with it. But I got the compile error "Method or data member not found".

So here's my questions. 1) Does anyone know what these objects are, and 2) is there any way to get hold of them in VBA and delete them? And 3) (for extra points) Does anyone know how these objects can appear in the worksheet hierarchy, but Excel can't find them?

Maybe somebody might want to do other things with them, but I'll be real pleased to just have a macro that deletes them all!

Thanks in advance...


PS. Sorry, code tags didn't work and I couldn't figure out how to widen the text.

 

[nigelk]

Try

Sub test()

Dim sh As Shape

For Each sh In Sheets("New").Shapes

sh.Delete

Next

End Sub

 

[dmars]

Try

Sub test()

Dim sh As Shape

For Each sh In Sheets("New").Shapes

sh.Delete

Next

End Sub

Most excellent! There are 260 HTMLChec objects and Shapes has exactly 260 items. Looping through wksht.Shapes and looking at each sh, the properties of these items identically match up with the HTMLChec objects, well enough that I can find them individually on the spreadsheet and watch them disappear when sh.Delete executes. So I can delete 'em all and won't get anything but these HTMLChec objects.

Bravo!!! Thank you, thank you, thank you!!!