Solved

Excel VBA - deleting all objects except graphics

Posted on 2011-09-19
3
373 Views
Last Modified: 2012-06-27
I have a worksheet with buttons and pictures.  The code I tried (below) deletes all shapes but I want to ONLY delete buttons (NOT the pictures).

How can I delete only buttons?

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

0
Comment
Question by:AndresHernando
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 350 total points
ID: 36558610
Which kind of buttons are they? Forms or ActiveX?
If Forms:
 iCount = ActiveSheet.Buttons.Count
            For i = iCount To 1 Step -1
                ActiveSheet.Buttons(i).Delete
            Next i

Open in new window

0
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 150 total points
ID: 36558639
if they are all named CommandButton you can use this

        iCount = ActiveSheet.Shapes.Count
            For i = iCount To 1 Step -1
if Mid(ActiveSheet.Shapes(i).Name, 1, 7) <> "Command" then
                ActiveSheet.Shapes(i).Delete
end if

            Next i
0
 

Author Comment

by:AndresHernando
ID: 36559286
rorya, thanks for your help (again).  Works perfectly.

Sam, thanks for your input.  Your solution adds value.  I can see where I can use this. Thanks!

I'll split points 350/150.  I hope that's OK.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now