Link to home
Create AccountLog in
Avatar of AFGPHXExcel
AFGPHXExcel

asked on

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

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!
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image
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

ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Thanks!