Solved

Excel - Remove shapes

Posted on 2000-03-15
4
584 Views
Last Modified: 2008-03-17
I have added shapes to my worksheet from VBA using the following code:

With tasksheet.Shapes

With .AddShape(msoShapeRectangle, tlh1, tlh2, brh1, brh2)

With .Fill
     .ForeColor.RGB = RGB(200, 200, 200)
     .BackColor.RGB = RGB(200, 200, 200)
     .Patterned msoPattern50Percent
End With

End With

End With

This appears to work well, but I need to be able to remove all the shapes on the worksheet using a button_click().

I have also placed textboxes using addtextbox(etc..) so I must remove these at the same time.

hope someone can help.
Thanks

/Bob
0
Comment
Question by:BobR
  • 2
4 Comments
 
LVL 5

Expert Comment

by:vboukhar
Comment Utility
You can use something like that:
for each c in activesheet.shapes
 c.delete
next
It removes texboxes too
0
 
LVL 9

Expert Comment

by:antrat
Comment Utility
Hi BobR

This one line should do it


ActiveSheet.DrawingObjects.Delete

good luck


antrat


0
 

Author Comment

by:BobR
Comment Utility
Thanks for the solutions, but I ommitted one item.
On the worksheet are several buttons and both your solutions remove them as well, can you change the solution to leave these buttons on the worksheet.
Sorry for the ommission.
/Bob
0
 
LVL 5

Accepted Solution

by:
vboukhar earned 100 total points
Comment Utility
Try this one:
For Each c In ActiveSheet.Shapes
 If c.Type <> msoOLEControlObject Then
  c.Delete
 End If
Next

Good luck!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will show you how to use shortcut menus in the Access run-time environment.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

772 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

10 Experts available now in Live!

Get 1:1 Help Now