asked on 5/14/2011

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!

Microsoft Excel

Last Comment

AFGPHXExcel

5/14/2011

Chris Bottomley

5/14/2011

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

Chris Bottomley

5/14/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

AFGPHXExcel

5/14/2011

ASKER

Thanks!

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

Followers

Top Experts

Get a personalized solution from industry experts

Ask the experts

"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23
"Your help has saved me hundreds of hours of internet surfing."
@fblack61
"Just a dang good service!"
@golferski
"Worth every penny."
Steve Hougom
"It’s been a life saver."
Maria Halt
"Best support site I’ve seen!"
Bob Schneider
"I would be lost without them."
@fblack61
"Saved my job multiple times."
Walt Forbes
"I love this site."
Maria Duwe
"Friendly respectful help."
Andrew Kowtalo
"Such top-notch experts."
@magento
"The best money I have ever spent."
@rwheeler23
"Beyond any comprehensible value"
George Morris
"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23

Read over 600 more reviews

TRUSTED BY