Deleting check boxes

[Tempy]

Greetings,

I need to delete a number of check boxes which i copied over from an
HTML document into Excel.

I have the code:
Sub DelAllCb()

Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.FormControlType = xlCheckBox Then
shp.Delete
End If
Next shp

End Sub
But this does not work, is it the format of the check box below?

=EMBED("Forms.HTML:Checkbox.1","")
Please heeellp!!
Tempy

 

[Tom Ogilvy]

See what you get with this

Dim obj as OleObject
for each obj in ActiveSheet.OleObjects
msgbox typename(obj.object)
Next


That will perhaps give you something you can use to identify which are the
checkboxes you want to delete.

 

[yogendra joshi]

Try this...

Sub del_check()
For Each chk In ActiveSheet.CheckBoxes
chk.Delete
Next
End Sub

 

[Tempy]

Hi Thom,

Could you help me with the full code please, i have treid and cannot get
it to work.

Thanks for your time

tempy

 

[Tom Ogilvy]

sub tester1()
Dim obj as OleObject
for each obj in ActiveSheet.OleObjects
msgbox typename(obj.object)
Next
End Sub

 

[Tempy]

Hi Tom,

Thank you, please excuse my ignorance but what delets the object ?
As you probably realise i am new to this game.

Tempy

 

[Tom Ogilvy]

Nothing deletes the object. This was the first step - finding out what
typename returns for the object.

Then you can use that as a condition for deleting the object. Assume
typename returns html_checkbox

then you could adapt the code to delete

sub tester2()
Dim obj as OleObject
for each obj in ActiveSheet.OleObjects
if lcase(typename(obj.object)) = "html_checkbox" then
obj.Delete
End if
Next
End Sub

note that html_checkbox is just an example. Using the first macro (tester1)
should show you what the typename is for these checkboxes.

 

[Tempy]

Hi Tom,

Thank you very much for your help. Further problem

Tom, could you please help me, i have a list of 2000 numbers, ranging
from 1 to 3110 & there are 4 columns of data.
I need to search for a number and get the corrosponding data in the
other columns. I can do that with a Vlookup, no problem, but the problem
is i could sometimes have more than one of a specific number with
different information in the other columns?

Tempy

 

[Tom Ogilvy]

Look at the excel vba help example for the FindNext command