Select/Delete worksheet "Text Boxes" using VBA
Hi Everyone,
I'm sure you're all familiar with Text Boxes:
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.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.
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,
Last edited by ConneXionLost; 04-09-2009 at 01:43 PM.
Docendo discimus.
Please consider:
- Thanking those who helped you. Click the reputation icon
in the contributor's post and add Reputation.
- Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
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
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,
Docendo discimus.
Please consider:
- Thanking those who helped you. Click the reputation icon
- Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
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.
ryloCode:Sub aaa() For i = ActiveSheet.Shapes.Count To 1 Step -1 ActiveSheet.Shapes(i).Delete Next i End Sub
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,
Docendo discimus.
Please consider:
- Thanking those who helped you. Click the reputation icon
- Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Success!!!
The code ran for a solid 10 minutes. Afterwards, the document was 5 megabytes lighter.
Thanks Rylo!!!
Docendo discimus.
Please consider:
- Thanking those who helped you. Click the reputation icon
- Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks