Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Selection
I have found that for some reason one of the tabs on my spreadsheet contains
thousands of blank "Text Boxes". The text boxes do not have borders so they are invisible thus causing a slowdown in that particular area of the spreadsheet tab. Is there a way to select them all and delete them in one shot? or, Can I create a macro that goes let's say from the first text box to about 55,000 and delete them one at a time? Please advise. Thank you. Tony. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Selection
This code will work for you assuming that you want to delete all of the
shapes on the sheet. Shapes include text boxes and combo boxes and pictures and command buttons and... Sub DeleteStuff() Dim shp As Shape For Each shp In ActiveSheet.Shapes shp.Delete Next shp End Sub -- HTH... Jim Thomlinson "Tony7659" wrote: I have found that for some reason one of the tabs on my spreadsheet contains thousands of blank "Text Boxes". The text boxes do not have borders so they are invisible thus causing a slowdown in that particular area of the spreadsheet tab. Is there a way to select them all and delete them in one shot? or, Can I create a macro that goes let's say from the first text box to about 55,000 and delete them one at a time? Please advise. Thank you. Tony. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Selection
This code will delete ALL TextBoxes (whether from the Drawing toolbar or
from the ActiveX ControlToolbox toolbar) on the specified worksheet (change my example worksheet name from the Sheet7 that I used to your worksheet's actual name)... Sub DeleteAllTextBoxesOnSheet() Dim O As OLEObject With Worksheets("Sheet7") .TextBoxes.Delete For Each O In .OLEObjects O.Delete Next End With End Sub -- Rick (MVP - Excel) "Tony7659" wrote in message ... I have found that for some reason one of the tabs on my spreadsheet contains thousands of blank "Text Boxes". The text boxes do not have borders so they are invisible thus causing a slowdown in that particular area of the spreadsheet tab. Is there a way to select them all and delete them in one shot? or, Can I create a macro that goes let's say from the first text box to about 55,000 and delete them one at a time? Please advise. Thank you. Tony. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Selection
Slight modification to speed it up for lots of shapes:
Sub DeleteStuff() Dim shp As Shape Application.ScreenUpdating = False For Each shp In ActiveSheet.Shapes shp.Delete Next shp Application.ScreenUpdating = True End Sub "Jim Thomlinson" wrote: This code will work for you assuming that you want to delete all of the shapes on the sheet. Shapes include text boxes and combo boxes and pictures and command buttons and... Sub DeleteStuff() Dim shp As Shape For Each shp In ActiveSheet.Shapes shp.Delete Next shp End Sub -- HTH... Jim Thomlinson "Tony7659" wrote: I have found that for some reason one of the tabs on my spreadsheet contains thousands of blank "Text Boxes". The text boxes do not have borders so they are invisible thus causing a slowdown in that particular area of the spreadsheet tab. Is there a way to select them all and delete them in one shot? or, Can I create a macro that goes let's say from the first text box to about 55,000 and delete them one at a time? Please advise. Thank you. Tony. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Selection
Thanks Jim and everybody for your support. The sheet contained more than
52,000 text boxes and I could get rid of all of them. I just do not know how in the world this may have happened. Thanks again. Tony. "Rick Rothstein" wrote: This code will delete ALL TextBoxes (whether from the Drawing toolbar or from the ActiveX ControlToolbox toolbar) on the specified worksheet (change my example worksheet name from the Sheet7 that I used to your worksheet's actual name)... Sub DeleteAllTextBoxesOnSheet() Dim O As OLEObject With Worksheets("Sheet7") .TextBoxes.Delete For Each O In .OLEObjects O.Delete Next End With End Sub -- Rick (MVP - Excel) "Tony7659" wrote in message ... I have found that for some reason one of the tabs on my spreadsheet contains thousands of blank "Text Boxes". The text boxes do not have borders so they are invisible thus causing a slowdown in that particular area of the spreadsheet tab. Is there a way to select them all and delete them in one shot? or, Can I create a macro that goes let's say from the first text box to about 55,000 and delete them one at a time? Please advise. Thank you. Tony. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object Selection | Excel Discussion (Misc queries) | |||
next object selection | Excel Programming | |||
Selection with one object | Excel Programming | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Object Type of a selection... counting rows in a selection | Excel Programming |