For each on shapes, avoiding ActiveX controls

[spacebouncer]

I have some code that runs for each shape on a sheet. The code starts with a conditional, checking the name and title of the shape.

The code fails if I have ActiveX objects on the sheet. I'm thinking I could get over this with "On Error..." but I'd rather have use an IF statement and Exit For if its the wrong type. Don't really know the best way to do this.

Advice much appreciated. Thanks

 

[Domenic]

Try...

    [COLOR=darkblue]Dim[/COLOR] Shp [COLOR=darkblue]As[/COLOR] Shape
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Shp [COLOR=darkblue]In[/COLOR] ActiveSheet.Shapes
        [COLOR=darkblue]If[/COLOR] Shp.Type <> msoOLEControlObject [COLOR=darkblue]Then[/COLOR]
            [COLOR=green]'etc...[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] Shp

Also, one can loop through specific shapes and form controls. Here are a couple of examples...

Oval shapes:

    [COLOR=darkblue]Dim[/COLOR] oOval [COLOR=darkblue]As[/COLOR] Oval
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oOval [COLOR=darkblue]In[/COLOR] ActiveSheet.Ovals
        MsgBox oOval.Name
    [COLOR=darkblue]Next[/COLOR] oOval

Form control checkboxes:

    [COLOR=darkblue]Dim[/COLOR] oCheckBox [COLOR=darkblue]As[/COLOR] CheckBox
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oCheckBox [COLOR=darkblue]In[/COLOR] ActiveSheet.CheckBoxes
        MsgBox oCheckBox.Name
    [COLOR=darkblue]Next[/COLOR] oCheckBox

Hope this helps!

 

[spacebouncer]

Domenic thats great. First code there was exactly what I was after. You the man!

 

[Domenic]

You're very welcome!