How do I get Excel VBA to identify a shape as a picture?

AFGPHXExcel
AFGPHXExcel used Ask the Experts™
on
I am asking Excel to count shapes on a worksheet and delete them. The goal is to delete buttons, but it is also a deleting a picture. Is it possible to differentiate between the buttons and picture during its count? The code is as follows:

iCount = ActiveSheet.Shapes.Count
            For i = iCount To 1 Step -1
               
                ActiveSheet.Shapes(i).Delete
            Next i

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Check the button typename, should catch all and leave validation ranges.

If oleobject then perhaps you will need oleobject but check if thT AFFECTS YOUR PICTURES USING A COPY FIRST.

CHRIS
Sub delShapes()
Dim sh As Object
Dim obj As Object
For Each sh In ActiveWorkbook.Sheets
    
    For Each obj In sh.Shapes
        Select Case TypeName(obj.DrawingObject)
            Case "Button", "OLEObject"
                obj.Delete
            Case Else
        End Select
    Next obj
Next sh

End Sub

Open in new window

Software Quality Lead Engineer
Top Expert 2011
Commented:
Caps lock ... sorry and the fragment I used checks every sheet so active sheet for butttons would be:

Chris


Sub delShapes()
Dim obj As Object

    For Each obj In activesheet.Shapes
        Select Case TypeName(obj.DrawingObject)
            Case "Button", "OLEObject"
                obj.Delete
            Case Else
        End Select
    Next obj

End Sub

Open in new window

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial