Solved: Deleting text boxes

[cart0250]

The below partial code deletes some sheets of a workbook and then delete some text-boxes from the remaining sheets. I am just wondering if there is a more efficient way to delete the shapes? I do not want to delete ALL the text boxes in the workbook, just certain ones.

Sub summShot()
Application.DisplayAlerts = False
Sheets(Array("G SHEET", "S SHEET", "M SHEET", "ST SHEET", "E SHEET", "N SHEET", _
"H SHEET", "P SHEET", "TR SHEET", "B SHEET")).Delete

Sheets("ben").Select
ActiveSheet.Shapes("Text Box 407").Cut
ActiveSheet.Shapes("Text Box 415").Cut
ActiveSheet.Shapes("Text Box 408").Cut
ActiveSheet.Shapes("Text Box 410").Cut
ActiveSheet.Shapes("Text Box 411").Cut
ActiveSheet.Shapes("Text Box 412").Cut

Sheets("Glen").Select
ActiveSheet.Shapes("Text Box 124").Cut
ActiveSheet.Shapes("Text Box 125").Cut
ActiveSheet.Shapes("Text Box 128").Cut
ActiveSheet.Shapes("Text Box 126").Cut
ActiveSheet.Shapes("Text Box 127").Cut

Sheets("luve").Select
ActiveSheet.Shapes("Text Box 157").Cut
ActiveSheet.Shapes("Text Box 158").Cut
ActiveSheet.Shapes("Text Box 163").Cut
ActiveSheet.Shapes("Text Box 159").Cut
ActiveSheet.Shapes("Text Box 160").Cut

....

[lucas]

I do not want to delete ALL the text boxes in the workbook, just certain ones.

Does this mean that you will want to delete all of the shapes from certain sheets......or some of the shapes off of certain sheets?

[cart0250]

I want to delete some of the shapes from certain sheets. There is only one shape per sheet that I want to keep, so if there is a way to delete all shapes in workbook except those with specified names, it would be helpful.

[lucas]

[vba]Sub delSomeshapes()
Dim Shp As Shape
For Each Shp In Sheets("Sheet2").Shapes
If Shp.Name <> "Oval 3" Then
Shp.Delete
End If
Next
End Sub[/vba]
should get you started. This is just for 1 sheet at a time......if I have time I will see if I can get it to work on the entire workbook.....

[lucas]

This should work on all sheets in a workbook...you will have to name specifically the shapes you wish not to delete. Should delete all shapes except those named Oval 3
[vba]
Sub delSomeshapesFromAllSheets()
Dim Shp As Shape
Dim wk As Worksheet
For Each wk In ActiveWorkbook.Worksheets
For Each Shp In wk.Shapes
If Shp.Name <> "Oval 3" Then
Shp.Delete
End If
Next
Next
End Sub

[/vba]

[cart0250]

perfect... thanks a lot

[lucas]

Great! Be sure to mark your thread solved using the thread tools at the top of the page.

[XLGibbs]

http://www.mrexcel.com/board2/viewto...203236#1203236

Cross post with multiple responses


If you are going to post in multiple forums, please post the links so multiple people don't waste time duplicating the effort to help you out.

[Norie]

http://www.ozgrid.com/forum/showthread.php?t=62209