Deleting Shapes in a worksheet

[Glen Mettler]

I have worksheets that contain 1 or more text boxes. I need to make copies
of these worksheets but then delete the text boxes.
I have looked at the shapes collection but I can't figure out how to
identify the number of text boxes, select them, and then delete them.

Need help.

Thanks
Glen

[Jake Marx]

Hi Glen,

Glen Mettler wrote:
> I have worksheets that contain 1 or more text boxes. I need to make
> copies of these worksheets but then delete the text boxes.
> I have looked at the shapes collection but I can't figure out how to
> identify the number of text boxes, select them, and then delete them.

Something like this should work to remove all ActiveX TextBox controls from
a Worksheet:

Sub RemoveActiveXTextBoxes()
Dim ole As OLEObject

For Each ole In Sheets("Sheet1").OLEObjects
If TypeOf ole.Object Is MSForms.TextBox Then
ole.Delete
End If
Next ole
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

[Glen Mettler]

not ActiveX. Native Excel textbox

Glen

"Jake Marx" <msnews@longhead.com> wrote in message
news:%23Mc07x1cFHA.2552@TK2MSFTNGP14.phx.gbl...
> Hi Glen,
>
> Glen Mettler wrote:
>> I have worksheets that contain 1 or more text boxes. I need to make
>> copies of these worksheets but then delete the text boxes.
>> I have looked at the shapes collection but I can't figure out how to
>> identify the number of text boxes, select them, and then delete them.
>
> Something like this should work to remove all ActiveX TextBox controls
> from a Worksheet:
>
> Sub RemoveActiveXTextBoxes()
> Dim ole As OLEObject
>
> For Each ole In Sheets("Sheet1").OLEObjects
> If TypeOf ole.Object Is MSForms.TextBox Then
> ole.Delete
> End If
> Next ole
> End Sub
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]

[Norman Jones]

Hi Glen,

Try:

ActiveSheet.TextBoxes.Delete

---
Regards,
Norman



"Glen Mettler" <glen.e.mettler@lmco.com> wrote in message
news:%23uBzwm1cFHA.3156@tk2msftngp13.phx.gbl...
>I have worksheets that contain 1 or more text boxes. I need to make copies
>of these worksheets but then delete the text boxes.
> I have looked at the shapes collection but I can't figure out how to
> identify the number of text boxes, select them, and then delete them.
>
> Need help.
>
> Thanks
> Glen
>
>

[Jake Marx]

Glen Mettler wrote:
> not ActiveX. Native Excel textbox

Then something like this should work:

Sub RemoveFormsTextBoxes()
Dim shp As Shape

For Each shp In Sheets("Sheet1").Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlEditBox Then
shp.Delete
End If
End If
Next shp
End Sub

However, Norman's suggestion seems to work, too. I don't recall seeing it
done that way before, and it doesn't come up in intelisense, but it's
simpler and probably faster than looping.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
]

[K Dales]

You don't really need to count them:
Dim MyShape as Shape
For Each MyShape in Sheets("SheetName").Shapes
MyShape.Delete
Next MyShape

"Glen Mettler" wrote:

> I have worksheets that contain 1 or more text boxes. I need to make copies
> of these worksheets but then delete the text boxes.
> I have looked at the shapes collection but I can't figure out how to
> identify the number of text boxes, select them, and then delete them.
>
> Need help.
>
> Thanks
> Glen
>
>
>

[K Dales]

I may have misinterpreted your question in my previous answer: Are there
also other shapes on the sheet (other than textboxes?). If so, you need to
find out what the shape is before deleting it:

Dim MyShape as Shape
For Each MyShape in Sheets("SheetName").Shapes
If MyShape.Type = msoTextBox Then MyShape.Delete
Next MyShape

"Glen Mettler" wrote:

> I have worksheets that contain 1 or more text boxes. I need to make copies
> of these worksheets but then delete the text boxes.
> I have looked at the shapes collection but I can't figure out how to
> identify the number of text boxes, select them, and then delete them.
>
> Need help.
>
> Thanks
> Glen
>
>
>

[Glen Mettler]

Norman Jones provided what I need.

ActiveSheet.TextBoxes.Delete

Soooo simple. Thanks

Glen

"Jake Marx" <msnews@longhead.com> wrote in message
news:%23Mc07x1cFHA.2552@TK2MSFTNGP14.phx.gbl...
> Hi Glen,
>
> Glen Mettler wrote:
>> I have worksheets that contain 1 or more text boxes. I need to make
>> copies of these worksheets but then delete the text boxes.
>> I have looked at the shapes collection but I can't figure out how to
>> identify the number of text boxes, select them, and then delete them.
>
> Something like this should work to remove all ActiveX TextBox controls
> from a Worksheet:
>
> Sub RemoveActiveXTextBoxes()
> Dim ole As OLEObject
>
> For Each ole In Sheets("Sheet1").OLEObjects
> If TypeOf ole.Object Is MSForms.TextBox Then
> ole.Delete
> End If
> Next ole
> End Sub
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]

[sriram_rj]

Hi Jake Marx,

Thanks very much. Its very simple and easy

Initially my file size was about 16 MB due to unwanted textboxes. Now its reduced to 1.2 mb.

Thanks again