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

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]

not ActiveX. Native Excel textbox

Glen

"Jake Marx" wrote in message
...
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 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]
]

Norman Jones provided what I need.

ActiveSheet.TextBoxes.Delete

Soooo simple. Thanks

Glen

"Jake Marx" wrote in message
...
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]

Hi Glen,

Try:

ActiveSheet.TextBoxes.Delete

---
Regards,
Norman



"Glen Mettler" wrote in message
...
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

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

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

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


--
sriram_rj
------------------------------------------------------------------------
sriram_rj's Profile: http://www.excelforum.com/member.php...o&userid=25097
View this thread: http://www.excelforum.com/showthread...hreadid=380150