Select/Delete worksheet "Text Boxes" using VBA

[ConneXionLost]

Hi Everyone,

I'm sure you're all familiar with Text Boxes:

You can add shapes and text boxes (text box: A movable, resizable container for text or graphics. Use text boxes to position several blocks of text on a page or to give text a different orientation from other text in the document.) to a worksheet by using the Drawing toolbar.

Did you know that if you click the text box button, then click on a spreadsheet (inserting a blank text box), then click off the text box, a small blank text box will remain hidden from view on your spreadsheet? Once hidden, the only way to find it is to slowly move your cursor over the Text Box edge, and watch the cursor momentarily change shape. Did you also know that if you copy or fill down the cells behind that text box, it will make as many copies of itself as you made for the cell? Not too dis-similar from a virus.

Imagine what would happen if you unknowingly had some of these text boxes on your main calculation sheet (200 columns by 2000 rows); one where you regularly copied formulae/cells for a period of over four years. Yes, (judging by their names e.g. "Text Box 29413") the thriving population of these invisible beasties is now in the neighbourhood of thirty thousand! The damn things are worse than fleas; and they're impairing the function of the whole workbook.

Help! Would someone please provide a VBA macro to find, select and delete these little devils from the spreadsheet (just this one spreadsheet)?

Cheers,

[rylo]

Hi

Do you want to remove all the textboxes or just specific ones? If specific, then how do you determine what you want to remove, and what you want to keep?

rylo

[ConneXionLost]

Hi Rylo,

I want to remove all the Text Boxes on this one sheet. There are other sheets in the book, but I don't want to disturb them.

If it is any easier to know, these Text Boxes are all blank.

Cheers,

[rylo]

Hi

This will remove all the shapes on the current sheet. If you have shapes other than textboxes that you want to keep, then come back again for a refinement.

Please Login or Register  to view this content.

rylo

[ConneXionLost]

Thanks Rylo,

I'll give it a run in the morning when I'm at work (that's where the spreadsheet is), then post the results.

Cheers,

[ConneXionLost]

Success!!!

The code ran for a solid 10 minutes. Afterwards, the document was 5 megabytes lighter.

Thanks Rylo!!!

[JulieD821]

I had this same problem and this macro worked perfectly )) THANK YOU!!!

Hi

This will remove all the shapes on the current sheet. If you have shapes other than textboxes that you want to keep, then come back again for a refinement.

Please Login or Register  to view this content.

rylo

[MattDRock]

Thanks much. Somehow I had 36,000 text boxes in a file. It's now usable again.