Delete Excel Text Boxes 1

[gtdown]

I have a macro that rolls through an Excel Sheet, formatting another sheet into the appropriate output format. However, the original sheet has text boxes that I want to delete. These text boxes are not always the same, and so I obviously can't just record a macro where I go around deleting every one of them.

Is there code to delete ALL text boxes from a sheet? Please advise, thanks!!!

Geoff
geoffrey.downes@ewu.ericsson.se

 

[DaleWatson123321]

Hi Geoff,

This will work...

Sub Delete_Boxes()
Set myDocument = Worksheets(1)
myDocument.Shapes.SelectAll
Selection.Delete
End Sub

Hope it helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Hi again,

This actually is cleaner, and more "generic"...

Sub Delete_Boxes()
ActiveSheet.Shapes.SelectAll
Selection.Delete
End Sub

Hope it helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[gtdown]

Thanks Dale, works perfect!

 

[xlbo]

1 word of warning tho - Don't have any other "shapes" on the sheet like rectangles / circles / lines etc as it will delete them as well....unless this is what you want. Other than that, you could modify slightly to read:
For Each Shape In Activesheet.Shapes
If shape.type = 12 Then -----(mso OLE control Object - ie from the controls toolbox. change to 10 if from the forms toolbar
Shape.delete
else
end if
Next

HTH
geoff